Dynamic Text to Columns, Loop through columns

[jacobrcotton]

Hi,

I have a series of data that, essentially, only needs to be "refreshed" before that data can be run through a new macro. IE, a macro go through each cell and {F2}+{ENTER}. But that takes too long, so i've written a macro that will go through each column and do a TextToColumn, simply without any criteria so that all it will do is refresh the data. and its great! but its also broken...code below

Based on the error thrown by VBA, its bugging out on the TextToColumns due to an invalid Range in the first line of the TextToColumns code. But Range(row,column) has never failed me before...i dont think.

Any assistance would be helpful.

Thanks!

Sub DataRefresh()


    Dim col As Integer
    
    col = 1
    
    Do Until col > 100
        
    Range(Cells(1, col), Cells(100000, col)).Select
    Selection.TextToColumns Destination:=Range(Cells(1, col)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True


    col = col + 1


    Loop
    
    Range("A1").Select


End Sub

 

[mporres77]

Try this:

Sub DataRefresh()




Dim col As Integer

col = 1

Do Until col > 100

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, FieldInfo:=Array(Array(1, 2), Array(2, 1)), TrailingMinusNumbers:=True


Loop
End Sub

 

[Fluff]

Maybe

Sub DataRefresh()
   Dim col As Long

   For col = 1 To 100
      Columns(col).TextToColumns Destination:=Cells(1, col), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
         Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
         :=Array(1, 1), TrailingMinusNumbers:=True
   Next col

   Range("A1").Select
End Sub

 

[jacobrcotton]

Fluff, this works perfectly. Thanks so much!

 

[Fluff]

Glad we could help & thanks for the feedback