Oct 16 2018 04:04 AM - edited Oct 16 2018 04:10 AM
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.
Oct 16 2018 02:29 PM
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