Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Text to Columns in VBA

Posted on 2011-10-25
8
Medium Priority
?
212 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:bryanscott53
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 37026166
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37026176
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37026214
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:bryanscott53
ID: 37026245
it will only ever be 1 comma that splits the data

Tommy, your solution failed at "colCount = UsedRange.Columns.Count"
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37026278
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
 

Author Comment

by:bryanscott53
ID: 37026653
Thanks Tommy this works great, do you know how to change this to start from column AR onwards?
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 2000 total points
ID: 37026660
AR is column 44 so change line 5 to
For i = 44 To colCount
0
 

Author Closing Comment

by:bryanscott53
ID: 37027019
brilliant thanks so much!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Introduction to Processes
Loops Section Overview

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question