Use VBA to run "text to columns' Loop on first row to convert from text to date format

I have a CSV file that downloads approximately 20-30 columns formatted as text. Here is an example of the origianl data that comes from the CSV file:

Week starting 04/22/13 - Resources - Percent historical utilization


I recorded a macro to remove the text from the cells, and then recorded a macro to run text to columns on each column. While the text to columns macro works for the current file, it does not work for all files since the number of columns changes. Can someone assist me with creating a macro that will loop through each column, run text to column (text format to date format) until an empty column is reached? The first column is always E1, but the last column can vary with each download. Here is a copy of the recorded macro:

Sub D_MatrixUtil()
'
' D_MatrixUtil Macro
' Convert Line 1 dates from text to date format
'

'

Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("F1").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("G1").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=6
Range("H1").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("I1").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("J1").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("K1").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("L1").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("M1").Select
Selection.TextToColumns Destination:=Range("M1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=6
Range("N1").Select
Selection.TextToColumns Destination:=Range("N1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("O1").Select
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("P1").Select
Selection.TextToColumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("Q1").Select
Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("R1").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("S1").Select
Selection.TextToColumns Destination:=Range("S1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=6
Range("T1").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("U1").Select
Selection.TextToColumns Destination:=Range("U1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("V1").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("W1").Select
Selection.TextToColumns Destination:=Range("W1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("X1").Select
Selection.TextToColumns Destination:=Range("X1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("Y1").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=4
Range("Z1").Select
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AA1").Select
Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AB1").Select
Selection.TextToColumns Destination:=Range("AB1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AC1").Select
Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=4
Range("AD1").Select
Selection.TextToColumns Destination:=Range("AD1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AE1").Select
Selection.TextToColumns Destination:=Range("AE1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AF1").Select
Selection.TextToColumns Destination:=Range("AF1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AG1").Select
Selection.TextToColumns Destination:=Range("AG1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=6
Range("AH1").Select
Selection.TextToColumns Destination:=Range("AH1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AI1").Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AJ1").Select
Selection.TextToColumns Destination:=Range("AJ1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AK1").Select
Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
Range("AL1").Select
Selection.TextToColumns Destination:=Range("AL1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
End Sub

Thank you WasWodge for the quick reply, the TTC code works with my data. Is there anyway to limit the code to the first row only?

I updated the code to:

Sub TTC()
Dim LstCo As Long, i As Long
Application.ScreenUpdating = False
LstCo = Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
For i = 1 To LstCo
Rows("1:1").Select
Columns(i).TextToColumns Destination:=Cells(1, i), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 3), TrailingMinusNumbers:=True
Next i
Application.ScreenUpdating = True
End Sub


Selecting the first row fixed my problem. Thanks again WasWodge!

I see what you are saying. After taking a look at my data and requirements, I will need to update several macros to account for varying number of columns in imported CSV file. Please see sample data attached (requirements are on the original data tab in italics).

Thank you, call function worked like a charm. I'll create another post for my other questions.