Removing "select" in this VBA Code

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Is there an easy way for me to remove this "select" option i tried using With sheet and defining the workbook and sheet together but then i mess up my comma delimited. Anyone able to assist? Sheet is called "data"

VBA Code:
Sub CommaDelimited()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 2)), TrailingMinusNumbers:=True
End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
have you tried substituting with activate
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
have you tried substituting with activate
Yes i can get it to work with activate and select. but was seeing if i could accomplish doing my comma delimited without activating or selecting anything.
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
403
Hi
Note: .Cells(1, 1) instead of Range("A1") to be first Cell of column you select.
VBA Code:
Sub CommaDelimited()

 MyCl = Columns(1).Address ' or 'MyCl =  Columns("A").Address
   With Columns(MyCl)
    .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 2)), TrailingMinusNumbers:=True
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,494
Messages
5,548,375
Members
410,830
Latest member
Tubtechbob
Top