Automatically Accept Replace Values in Text-to-Columns macro
 

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

Automatically Accept Replace Values in Text-to-Columns macro
 

Have you tried

Application.DisplayAlerts = FALSE
and
Application.Displayalerts = TRUE

before and after the Text To Columns?
--
HTH,
Barb Reinhardt



" wrote:

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

Automatically Accept Replace Values in Text-to-Columns macro
 

Thank you Barb, that did it.


Steven

On Jul 30, 7:25*pm, Barb Reinhardt
wrote:
Have you tried

Application.DisplayAlerts = FALSE
and
Application.Displayalerts = TRUE

before and after the Text To Columns?
--
HTH,
Barb Reinhardt

" wrote:
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