VB Text to column

[lo1ly]

I'm not sure what I'm doing wrong. I've tried multiple things and looked at different threads, but i still get the Run-time Error '1004'. I'm just trying to do text to column for range A:A in Sheet "LY".

This is the code I've been using

Sheets("LY").Select


            Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

I've tried changing the range and messing with the other settings as well..


Any help is appreciated

Thanks!!

 

[lo1ly]

Nevermind. I fixed it myself using a Dim. Thanks for looking though!

Dim objRange As Range


Set objRange = Range("A6:A350")






Sheets("LY").Activate


            objRange.TextToColumns _
                Destination:=Range("A6"), _
                DataType:=xlDelimited, _
                TextQualifier:=xlSingleQuote, _
                ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, _
                Comma:=True, _
                Space:=False, _
                Other:=False

 

[L. Howard]

I can produce your error with your code if I do not select the data in column A. That is, I have the cursor sitting on a empty cell.

Your code wants a selection of comma delimited data to do its work. Which it does for me with data in A2:A10, and selected.

Maybe try this. No need to select.

Hoard

  Sheets("LY").Range("A2", Range("A2").End(xlDown)).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

 

[mole999]

This was just a short play with 5 values from two rows

    Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True

I think you are selecting the sheet and then telling the vba to change a column, when its expecting a sheet

I'd be tempted to record one from the longest list you have, and potentially avoid trying to do that for a million rows