paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
644
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

Code:
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Try this instead:

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

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
644
Perfect, worked like a charm, I'd been stuck on this for an hour

Many thanks

cheers

Paul
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,063
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top