Automating Text to Columns -- can I auto-click "OK" on the message

[robs3131]

Hi,

As part of my code, I am performing a text to columns separation. During
this process, an Excel generated message comes up asking if I would like to
replace the contents of the cells in the columns where the data is being
separated to -- is there any way I can automate the clicking of "OK" on this
message box? Or not have it come up at all? Below is my code. Thanks!

With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With

 

[Gary Keramidas]

you can try

application,displayalerts = false
With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With
application.displayalerts = true

 

[Mike Fogleman]

Typo in first line:
application,displayalerts = false
comma should be a period
application.displayalerts = false

Mike F