Loop text to columns procedure

Question

I know that you can select an entire string to do this however, that doesnt seem to work for me. Some of the cells have single spaces inbetween the text and some of the cells have multiple spaces in which I need to separate into different columns at each space and when i do the entire column at once, it reads multiple spaces as one space.

So, I need to replace this with a loop that goes for the Range("l3:i10000") until the first empty cell

 Range("I3").Select

    Selection.TextToColumns Destination:=Range("I3"), DataType:=xlDelimited, _

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

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

        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _

        True

    Range("I4").Select

    Selection.TextToColumns Destination:=Range("I4"), DataType:=xlDelimited, _

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

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

        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _

        True

    Range("I5").Select

    Selection.TextToColumns Destination:=Range("I5"), DataType:=xlDelimited, _

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

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

        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _

        True

Answer 1

Hi,

Try this

Sub Macro1()

Dim last_row As Integer

last_row = Worksheets("sheet1").Range("I65000").End(xlUp).Row

For n = 3 To last_row

    Range("I" & n).TextToColumns Destination:=Range("I" & n), DataType:=xlDelimited, _

    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Space:=True, FieldInfo _

    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _

    TrailingMinusNumbers:=True

Next n

End Sub

Change sheet1 to your tab name.

Hope this helps.

Answer 2

Thanx!

When I make my first million.... I'm buying you a Krystal WITH cheese!! (...Or a White Castle if you prefer)