0

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
braX
  • 11,506
  • 5
  • 20
  • 33
Wouter Groeneweg
  • 43
  • 1
  • 8
  • 1
    So possibly a `.usedrange.columns.count`? Then loop through columns, if column has used rows > X then `.texttocolumns`? – Cyril May 04 '20 at 18:42
  • Do you want to find the last (bottom-most) non-empty cell in the last (right-most) non-empty column or something else? Note, there might be data in rows below in the columns before. In your case there might be data in cell `B4` when you might want to get cell `C4` although it is empty. – VBasic2008 May 04 '20 at 21:23
  • If you could explain where your data to be `TextToColumn-ed` is, and where you want to put the result e.g my data is in `A1:A5` , I want to put it into column `C`, but I don't want to overwrite data in e.g. `C3`, I want to start writing from `C4`... – VBasic2008 May 04 '20 at 21:48
  • @VBasic2008 I want indeed the last (right most) non empty cell, so in the third picture `C3`. Maybe there is in my code to splits the data (I'm not really able to understand all the code), but I want to format the numbers from text to numbers. – Wouter Groeneweg May 05 '20 at 08:21

0 Answers0