Search code examples
excelvbatext-to-column

Text To Columns VBA

I'm trying the text to column but seems I have a error. This is my Code:

Set rg = Range("H3:H" & lr).CurrentRegion
rg.TextToColumns _
Destination:=Range("H3"), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="Chr(10)"

enter image description here

Solution

  • Please, test the next way. It splits the cells of column H:H by Chr(10):

    dim rg as Range
set rg=Range("H1").Entirecolumn
rg.TextToColumns destination:=rg, DataType:=xlDelimited, Other:=True, OtherChar:=Chr(10)

    or splitting only the column filled range:

    dim rg as Range
set rg=Range("H1:H" & Range("H" & rows.count).End(xlUp).Row)
rg.TextToColumns destination:=rg, DataType:=xlDelimited, Other:=True, OtherChar:=Chr(10)

    I can show you how to (theoretically) run the code for all columns of CurrentRegion, but such an approach will be at least strange. I mean, splitting the first range column, all the rest of the range will be overwritten by the splitting process, everything will be ruined and nothing to be split remains...