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
– enderland, Commented Sep 14, 2012 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
– user1112251, Commented Sep 14, 2012 at 20:14

masterxilo · Accepted Answer · 2018-10-23 08:41:15Z

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, 2018 at 8:41

masterxilo

2,8263 gold badges36 silver badges37 bronze badges

answered Sep 14, 2012 at 20:14

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user1112251 Over a year ago

Hello CHris, I tried your solution but I got this error, "Microsoft Excel can only convert one column at one time ... ". Any ideas?

chris neilsen Over a year ago

Updated to account for possibility range is not column A

user1112251 Over a year ago

That worked like a charm I just added the Worksheet("Sheet") no the range :), Thanks Chris

user1112251 Over a year ago

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 Over a year ago

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

|

Bhanupratap Tomar · Accepted Answer · 2017-07-04 08:34:49Z

1

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.

answered Jul 4, 2017 at 8:34

Bhanupratap Tomar

111 bronze badge

Collectives™ on Stack Overflow

Convert text to columns in Excel using VBA

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related