If next column is blank move to next function

Copper Contributor

Hi,

 

I'm using the following code to convert the attached range into numbers format. I'm using the text to column function to do so. 

 

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

 

So for each column i have to convert, i have a separate block. But the problem is, the files i need to run this macro for might have varying number of columns. The full macro i currently have does this for 111 columns. But if a file has less than 111 columns, the execution will stop halfway. 

 

Can i do something like this instead?

1. Execute the block once,

2. Go to the next column,

3. If the first cell of the selected column has a value, continue the loop,

4. If not, go to next,

 

Thanks in advance!

 

PS: I really don't know about the terminology. I hope it's okay. 

1 Reply

Sure, that's exactly what Do / Loop  is for.  You just have to make the references dynamic.  This should run through every column until the top cell of the column is blank.

Dim i As Integer
i = 1
Do While Cells(1, i).Value <> ""

Columns(i).Select
Selection.TextToColumns Destination:=Cells(1, i), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
i = i + 1

Loop