Text to columns

[paulsolar]

Hi all

Probably a very simple question, but I cant find an answer anywhere on line. What do I need following Other:True for a Colon?

I know how to do it in the sheet but I need to do it with vba

thanks

Paul

Sub TextToColumnsRangeSelection()
    On Error Resume Next
    Dim MySelection As Range


    Application.DisplayAlerts = False
    Set MySelection = Sheet1.Range("A1:A7")


    MySelection.TextToColumns _
    Destination:=MySelection, _
    DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=True
End Sub

 

[jproffer]

Try this instead:

MySelection.TextToColumns _
    Destination:=MySelection, _
    DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Otherchar:=":"

 

[paulsolar]

Perfect, worked like a charm, I'd been stuck on this for an hour

Many thanks

cheers

Paul

 

[Joe4]

What do I need following Other:True for a Colon?

I know how to do it in the sheet but I need to do it with vba

Here is a little tip/trick that will allow you to get these sort of things quickly and easily.
Turn on the Macro Recorder, and record yourself doing it manually.
Then stop the recorder and look at your code.
It will show you exactly what that argument needs to look like!