Dynamic Range: Text To Columns

  • How would I modify this macro to handle a [dr]*[/dr] In the example below A27 is the last row in my data set, but its last row number is dynamic.


    I'm using this code just after finding the last row in the dataset, but found it breaks if the last row doesn't fall in the A27 range.


    I find the last row prior to executing the TextToColumns code.


    Code
    Range("A65536").End(xlUp).Select


    Code
    Sub TextToColumns()
        Selection.TextToColumns Destination:=Range("A27"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
    End Sub


    Thanks in advance!

  • Re: Dynamic Range - Text To Columns


    How about something like this....

    Code
    Sub TextToColumns()
        Dim myRange As Range
        
        Set myRange = Range("A65536").End(xlUp)
        
        Selection.TextToColumns Destination:=Range(myRange.Address), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
    End Sub
  • Re: Dynamic Range - Text To Columns


    I ran the macro but it asks: "Do you want to replace the contents of the destination cells?"


    If I choose OK it does not perform the Text To Columns properly and if I choose Cancel it says: "Run-time error '1004': TextToColumns method of Range class failed."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!