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)
Share
Improve this question
asked Jan 17 at 20:59
Helos35Helos35
1211 silver badge13 bronze badges
2
|
3 Answers
Reset to default 1The 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)
Selection.Cells(1).CurrentRegion
– Spectral Instance Commented Jan 17 at 21:27