Cfrgtkky

I'm using the following codes repeatedly to text to column some data. I'm repeating these lines from ("A:A") to ("DM:DM"). Sometimes the file i want to run this on will have less that that amount of columns. Even if that's the case i made the code to text to column the maximum possible amount of columns. But this makes the execution slow. I want to convert this to a loop where if the next column is blank, the execution will jump to the next block.

Can you good people suggest me a way to do that?

Range("A:A").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

Thank you!

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x