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.
Could you add a sample workbook so we can see what is going on and save us from recreating the data?
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")
