Automatically Accept Replace Values in Text-to-Columns macro

[sbitaxi]

Hello:

Working on a macro to format a report and strip out unnecessary dates,
but in order to do that I need to separate the time from the date. I
first insert a column after the column containing the date field
(DateFld, there are a few of them), then TextToColumns delimited by
"T" for time, and format the date field to a date format.

Every time it gets to the .TextToColumns step, it asks if I want to
replace the data and I want it to automatically do it without prompt.

I know this code could be cleaner, but I'm pretty green at this. Here
it is!

For Each Thing In DateFld
Columns(Thing + 1).Insert Shift:=xlRight
Columns(Thing).Select

With Selection.Columns
.TextToColumns Destination:=Columns(Thing), Other:=True,
OtherChar:="T"
.NumberFormat = "m/d/yyyy"
End With

Rng.AutoFilter Field:=Thing, Criteria1:="<" & RptDate
Range("3:" & Last).SpecialCells(xlCellTypeVisible)
(1).EntireRow.Delete
WS.AutoFilterMode = False
Rng.AutoFilter Field:=Thing, Criteria1:=">=" & EndRptDate
Range("3:" & Last).SpecialCells(xlCellTypeVisible)
(1).EntireRow.Delete
WS.AutoFilterMode = False
Next

Thank you!

Steven

 

[Barb Reinhardt]

Have you tried

Application.DisplayAlerts = FALSE
and
Application.Displayalerts = TRUE

before and after the Text To Columns?

 

[sbitaxi]

Thank you Barb, that did it.


Steven

Have you tried

Application.DisplayAlerts = FALSE
and
Application.Displayalerts = TRUE

before and after the Text To Columns?