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
bryanscott53Asked:
Who is Participating?
 
TommySzalapskiCommented:
AR is column 44 so change line 5 to
For i = 44 To colCount
0
 
nutschCommented:
Can you have more than one comma per cell?
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
0
 
TommySzalapskiCommented:
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TommySzalapskiCommented:
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.
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 Sub

Open in new window

0
 
bryanscott53Author Commented:
it will only ever be 1 comma that splits the data

Tommy, your solution failed at "colCount = UsedRange.Columns.Count"
0
 
TommySzalapskiCommented:
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:
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 Sub

Open in new window

0
 
bryanscott53Author Commented:
Thanks Tommy this works great, do you know how to change this to start from column AR onwards?
0
 
bryanscott53Author Commented:
brilliant thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.