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

Have you tried

Application.DisplayAlerts = FALSE
and
Application.Displayalerts = TRUE

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



"(E-Mail Removed)" 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
>

Thank you Barb, that did it.


Steven

On Jul 30, 7:25*pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> Have you tried
>
> Application.DisplayAlerts = FALSE
> and
> Application.Displayalerts = TRUE
>
> before and after the Text To Columns?
> --
> HTH,
> Barb Reinhardt
>
> "sbit...@gmail.com" 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