(Solved) – Excel VBA: Text to Columns with blank cells

(solved)-–-excel-vba:-text-to-columns-with-blank-cells

I saw the Post of J.Con about the text to column loop (Text to columns for multiple columns – Excel VBA). However this only works if the first row has all the information, like in first picture:enter image description here

So I changed the code so it is working like in the second picture. This one works a sort of. Only I want that it works like in the third pictures that it finds the last column where the cell is not blank, so in this case cell(“C3”)
enter image description here
enter image description here

The code I use:

    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", _
                        After:=Range("A1"), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        searchDirection:=xlPrevious, _
                        MatchCase:=False).Column

    Dim LastColumn As Long
    LastColumn = Cells.Find(What:="*", _
                        After:=Range("A1"), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        searchDirection:=xlPrevious, _
                        MatchCase:=False).Column


'Loops Text to columns
    Dim StartingRow, StartingColumn As Long
    StartingRow = 1

    For StartingColumn = 1 To LastColumn
        Range(Cells(StartingRow, StartingColumn), Cells(LastRow, StartingColumn)).Select

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

    Next

Leave a Reply

Your email address will not be published. Required fields are marked *