Log In
Register
Home
New Member
January 10, 2020
Offline
I need VBA code that will select the first column in a workbook, perform Text to Columns (for the purpose of converting any numbers stored as text to numbers), then loop thru all the columns in the worksheet doing the same thing. I get the TextToColumns code; I always have trouble with loops. Could someone please help? Here is what I have so far:
Sub TextToColumns()
'Counts number of Columns (my headers start in row 1)
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Do
Selection.TextToColumns Destination:=ActiveCell.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1). ' I'm missing something here
Loop Until ' I'm missing something here
End
If I am going about this the wrong way I am open to a different method. Thanks in advance.
Trusted Members
December 20, 2019
Offline
Could you add a sample workbook so we can see what is going on and save us from recreating the data?
January 17, 2020
Offline
Text to Columns can only be applied to one column at a time. However, you can store all the columns you want to apply it to in an array and loop through that array, like this:
Sub MyTtoC()
Dim cols As Variant
Dim i As Long
Dim c As String
Dim rng As Range
Application.ScreenUpdating = False
' List columns you want to apply Text to Columns to
cols = Array("D", "T", "U")
' Loop through array
For i = LBound(cols) To UBound(cols)
' Get column letter
c = cols(i)
' Build range
Set rng = Range(c & ":" & c)
' Perform Text to Columns
With rng
. Text To Columns DataType:=xlDelimited
.NumberFormat = "General"
End With
Next i
' Name sheet
ActiveSheet.Name = "YOUR SHEET NAME"
Application.ScreenUpdating = True
End Sub
So, to add/remove columns, simply update this line here:
cols = Array("D", "T", "U")
1 Guest(s)
