-1

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

braX
  • 11,506
  • 5
  • 20
  • 33
cyrnsnpdr
  • 15
  • 4
  • Why is `"Chr(10)"` in quotes? Chr is a function. VBA sees it as a string since it's in quotes. I'm not sure if that solves the problem but it's definitely A problem. – Simon Dec 28 '21 at 07:01
  • 2
    Did you read the error message? It seems that `rg` contains more than one column because you used `CurrentRegion`. – Storax Dec 28 '21 at 07:27
  • As the error description states, this method works only on **a column at a time**. Then, please explain **in words** what you try accomplishing. The method can be used for different scopes. Do you want splitting cells content by `Chr(10)`? – FaneDuru Dec 28 '21 at 07:28

1 Answers1

1

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...

FaneDuru
  • 38,298
  • 4
  • 19
  • 27