Home

If next column is blank move to next function

Shanike De Silva
Occasional Visitor

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
Highlighted

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


Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
207 Replies