Text-to-Column fail if column empty

[BillTony]

I am attempting to do a Text-to-Column conversion using VBA, and am getting a run-time 1004 (No data selected to parse) if the column is completely empty.

This, of course, makes sense.

However, how do I get past this?

The source data used is formatted as text and the formatting is beyond my control - however, I need to convert the data to a number in order to perform any meaningful calculations.

I have numerous columns in the worksheet that I need to perform this conversion on and each of these columns may, or may not, contain data on a given day.

So I need to perform the conversion, if applicable, on a given column then move to the next and repeat as necessary.

The code I am currently utilizing is below.

Thanks in advance!

'Column / RANGE 1.
'CONDITIONAL FILL.  

    If Last_Row_ColA > 3 Then
        Range("AA3:AA" & Last_Row_ColA).Select
        Selection.TextToColumns Destination:=Range("AA3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Else
        Range("AA3").Select
        Selection.TextToColumns Destination:=Range("AA3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    End If

 

[Fluff]

Simply delete the Else part of the If...Else

 

[BillTony]

Thanks for the input, but...

The last row of Column A (an "anchor" point or controlling variable) will never or, hardly ever, be blank - this is the 2nd line in the If Statement.

So it is still failing because there is no data in column AA to be converted.

I subsequently run the same procedure in a variety of columns (AD, BC, BL, etc.) throughout the worksheet - which "may" contain data on a given day.

How do I get around that?

 

[Fluff]

You need to work out the last row in each column, not base it on what's in another column.

 

[BillTony]

Thanks!

 

[Fluff]

You're welcome