How to run Text to Columns only IF column contains

问题:

I need to run a text to columns MACRO on a range, which varies based on the data imported. I have the split vba already, simply by recording it, but it gives me a "no data to parse" error when the column is blank. There could be 2 columns all the way to 10, so I need to have the vba ignore the "split text to columns" if the column is blank.

Fairly new with vba, so the code is basic, but it does not to be complex. Here is the split that I have recorded for one of the columns:

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

It also splits columns E, G, I, and K

回答1:

You could create a loop, that looks at each column, and if the CountA() is 0, it means there's no data in there.

Sub t()
Dim myCols() As Variant
Dim i As Long

myCols = Array(3, 5, 7, 9, 11)

For i = LBound(myCols) To UBound(myCols)
    If WorksheetFunction.CountA(Columns(myCols(i))) <> 0 Then
        Columns(myCols(i)).TextToColumns Destination:=Cells(1,myCols(i)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End If
Next i

End Sub

And if you need to add/subtract columns, just edit the myCols array.