Convert text to columns in Excel using VBA

Question

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

What have you tried? If you post the code you've used you should immediately see how to do this (unless I totally misunderstood your question). — enderland, Sep 14 '12 at 20:06
I have tried, but it only works if I only have something selected, I want to make the selection dynamic (A7:A50000? or even better A7:lastnonempty cell?) — user1112251, Sep 14 '12 at 20:14

score 13 · Accepted Answer · edited Oct 23 '18 at 08:41

13

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

edited Oct 23 '18 at 08:41

masterxilo

2,503
1
30
35

answered Sep 14 '12 at 20:14

chris neilsen

52,446
10
84
123

Hello CHris, I tried your solution but I got this error, "Microsoft Excel can only convert one column at one time ... ". Any ideas? – user1112251 Sep 14 '12 at 20:25
Updated to account for possibility range is not column `A` – chris neilsen Sep 14 '12 at 20:32
That worked like a charm I just added the Worksheet("Sheet") no the range :), Thanks Chris – user1112251 Sep 14 '12 at 20:41
One more questions, if I needed to do this for 2 columns, let's say column A and B, could I do it in a cycle or I should run this procedure 2 times? – user1112251 Sep 14 '12 at 20:45
I tried to add another block of code but now with rng2 = [D], and it's not able to do it :(, it wont let me capture the range of the new column – user1112251 Sep 14 '12 at 20:56
It seems that when I try to assing the new cell range and last column it crashes, `Set rngCRM = [O6] Set rngCRM = Sheets("Sheet2").Range(rngCRM, Cells(Rows.Count, rngCRM.Column).End(xlUp)) rngCRM.TextToColumns Destination:=rngCRM, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True` Issue comes exaclty on the part where we get the last column – user1112251 Sep 14 '12 at 21:15
`Set rngCRM = [O6]` sets `rngCRM` to `O6` on the active sheet. Use `Set rngCRM = Sheets("Sheet2").[O6]` instead – chris neilsen Sep 14 '12 at 21:30
(http://stackoverflow.com/a/10717999/445425) may help understand range references – chris neilsen Sep 14 '12 at 21:33
Got it, one more thing and I'll leave you alone, this is only working when I'm in sheet2, (but the buttong is on sheet1), is there way to make it work if I click the buttong on Sheet1? – user1112251 Sep 14 '12 at 21:52
Can you explain what `TextQualifier` and `fieldinfo` is? I probably can omit it. I have text separated by comma without double quotes in one column. – Timo Jun 07 '21 at 14:45
I get syntax error from `rng.TextToColumns Destination:=rng, DataType:=xlDelimited,` down to line before `end with`. – Timo Jun 07 '21 at 14:51
@timo [have you seen this](https://learn.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns) – chris neilsen Jun 07 '21 at 18:08

score 1 · Answer 2 · answered Jul 04 '17 at 08:34

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.

Convert text to columns in Excel using VBA

2 Answers2

Linked