Text to Columns in VBA
Hi everyone, i have a number of columns in a worksheet separated by a comma. The number of columns may change and is not fixed. I have tried using the macro recorder and looked online but cannot seem to work this out. i need each of the columns split but do not want to lose any data? I hav attached a sample of the work book for you. SAMPLE.xlsx
If there's always one comma per cell then something like this should work.
Public Sub TextToCols()
colCount = UsedRange.Columns.Count
For i = 1 To colCount
Columns(2 * i).Insert
Columns(2 * i - 1).TextToColumns Comma:=True
Next
End Sub
Of course for that many columns it will be slow so you'll want to turn off screen updating.
Every time you do that, you need to handle crashes and turn it back on even if there's an error. So use this and use the original code (comment out the new lines) if you need to debug.
Every time you do that, you need to handle crashes and turn it back on even if there's an error. So use this and use the original code (comment out the new lines) if you need to debug.
Public Sub TextToCols()
On Error GoTo crash
Application.ScreenUpdating = False
colCount = UsedRange.Columns.Count
For i = 1 To colCount
Columns(2 * i).Insert
Columns(2 * i - 1).TextToColumns Comma:=True
Next
crash:
Application.ScreenUpdating = True
End SubASKER
it will only ever be 1 comma that splits the data
Tommy, your solution failed at "colCount = UsedRange.Columns.Count"
Tommy, your solution failed at "colCount = UsedRange.Columns.Count"
That is just because of where the code is. I was assuming the code was in a particular sheet.
Add
Worksheets("Sheet1").
(or whatever the sheet's name is)
in front of UsedRange and Columns like this:
Add
Worksheets("Sheet1").
(or whatever the sheet's name is)
in front of UsedRange and Columns like this:
Public Sub TextToCols()
On Error GoTo crash
Application.ScreenUpdating = False
colCount = Worksheets("Sheet1").UsedRange.Columns.Count
For i = 1 To colCount
Worksheets("Sheet1").Columns(2 * i).Insert
Worksheets("Sheet1").Columns(2 * i - 1).TextToColumns Comma:=True
Next
crash:
Application.ScreenUpdating = True
End SubASKER
Thanks Tommy this works great, do you know how to change this to start from column AR onwards?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
brilliant thanks so much!
Do the columns need to match, i.e. if cell A2 has three commas, and A3 has two, does B3 start at column C or column D?
Thomas