Is there a good way to get the resulting range after using Range.TextToColumns in VBA?

What I'm trying to do is split some data into columns, and then perform an action on each resulting cell. If I knew ahead of time how big the resulting range would be I could just select it, but I want to do it dynamically for any size of data.

Call Selection.TextToColumns(DataType:=xlDelimited, comma:=True)
resultRange = ?????
Call OtherSub(resultRange)

Is there a good way to get the resulting range after using Range.TextToColumns in VBA?

What I'm trying to do is split some data into columns, and then perform an action on each resulting cell. If I knew ahead of time how big the resulting range would be I could just select it, but I want to do it dynamically for any size of data.

Call Selection.TextToColumns(DataType:=xlDelimited, comma:=True)
resultRange = ?????
Call OtherSub(resultRange)

• excel
• vba

• Selection.Cells(1).CurrentRegion – Spectral Instance Commented Jan 17 at 21:27
• Iterate through selection and use Ubound(Split(Selection.Cells(n,1),",")) for each cell to get the number of columns, then just track which is your Max. – Frank Ball Commented Jan 24 at 19:03

3 Answers 3

The sample script assumes that all rows in your data contain an equal number of columns after the splitting process.

Using CurrentRegion is a straightforward approach (as @Spectral Instance's comment) if there are no blank columns after splitting. Otherwise, additional code is needed to determine the number of columns.

Microsoft documentation:

Split function

Range.CurrentRegion property (Excel)

Sub demo()
    Dim r As Range
    With Range("A1:A3")
        .TextToColumns DataType:=xlDelimited, comma:=True
        Set r = .CurrentRegion
        Debug.Print r.Address  ' => $A$1:$C$3
    End With
    
    With Range("A7:A9")
        Dim sTxt As String: sTxt = .Cells(1)
        .TextToColumns DataType:=xlDelimited, comma:=True
        Set r = .CurrentRegion
        Debug.Print r.Address  ' => $A$7:$B$9 only get the first block cells
        Dim iCol As Long: iCol = UBound(Split(sTxt, ",")) + 1
        Set r = .Resize(, iCol)
        Debug.Print r.Address  ' => $A$7:$E$9
    End With
End Sub

For example (assuming there is no data already present to the right of cells to be split):

Dim f As Range

With ActiveSheet.Range("A8:A10")
    'these next 2 lines are for testing only....
    .EntireRow.Clear
    .Value = .Parent.Range("A2:A4").Value


    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Space:=True, Other:=True, OtherChar:="|"
    Set f = .EntireRow.Find(what:="*", LookIn:=xlValues, _
              searchorder:=xlByColumns, SearchDirection:=xlPrevious)
    
    Debug.Print "Last column: " & f.Column '>>8 in this case
    
End With

Values before and after splitting:

I ended up just manually interrogating the data to figure out the result. It seems like this puts me most of the way to just re-coding text to columns anyway, but it works.

Set selectedRange = Selection

maxCol = 0
For Each rng In selectedRange
    sTxt = rng.Value
    iCol = UBound(Split(sTxt, ",")) + 1
    If iCol > maxCol Then
        maxCol = iCol
    End If
Next rng

Call Selection.TextToColumns(DataType:=xlDelimited, _
    comma:=True, _
    Space:=False, semicolon:=False, Tab:=False, other:=False, _
    ConsecutiveDelimiter:=False)

Set rng = selectedRange.Resize(, maxCol)