VBA Code for Text to Column

[Shweta]

Hi All,

I have a problem with coding of text to column.
I have recorded a macro for text to column but when I run it, a message comes up " Do you want to replace the column".

How can I stop that?

I want the macro run on a single click on command button without asking for anything.

Thanks,
Shweta

 

[kpark91]

Turn off alerts

Application.DisplayAlerts = False

and don't forget to turn it back on after.

 

[Scott Huish]

Surround your code with Application.DisplayAlerts=False at the beginning and Application.DisplayAlerts=True at the end, like this:

Sub Macro1()
Application.DisplayAlerts = False
    Columns("A:A").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)), TrailingMinusNumbers:= _
        True
Application.DisplayAlerts = True
End Sub

 

[Shweta]

Thanks Hotpepper & Kpark91!

I used "application.displayalerts = false" but still the msg box is coming.

Shweta

 

[Scott Huish]

Where did you put it?

Can you post the code as you have it?

 

[Shweta]

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub