I'm trying to convert text to columns using a macro but I'm not able to do it, I have tried to record a macro to achieve this, however I'm running into some issues since the text to columns VBA function expects a selection, is there a way I can dinamically chose let's say range A7:A50000? or even better A7:lastnonempty cell?
Thanks,
FYI, the delimeter is not important since I need to do this to convert text into formula
Here is my code
Range("O6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("O6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Try this
Sub Txt2Col()
Dim rng As Range
Set rng = [C7]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, ' rest of your settings
Update: button click event to act on another sheet
Private Sub CommandButton1_Click()
Dim rng As Range
Dim sh As Worksheet
Set sh = Worksheets("Sheet2")
With sh
Set rng = .[C7]
Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True,
Space:=False,
Other:=False, _
FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, xlGeneralFormat)), _
TrailingMinusNumbers:=True
End With
End Sub
Note the .
's (eg .Range
) they refer to the With
statement object
If someone is facing issue using texttocolumns function in UFT. Please try using below function.
myxl.Workbooks.Open myexcel.xls
myxl.Application.Visible = false `enter code here`
set mysheet = myxl.ActiveWorkbook.Worksheets(1)
Set objRange = myxl.Range("A1").EntireColumn
Set objRange2 = mysheet.Range("A1")
objRange.TextToColumns objRange2,1,1, , , , true
Here we are using coma(,) as delimiter.
Source: Stackoverflow.com