I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

I'd apply data|filter|autofilter

Filter to show the rows that need to be deleted and then delete those visible
rows.

Acute Mind wrote:

I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

--

Dave Peterson

Hello Mr. Peterson,

Is there a way to do the autofilter without having to literally choose the
date each time? I mean, is there a way to set the autofilter to today's date
all the time because if I get this working, my co-worker will be using it as
well on my day's off, and she doesn't understand filtering or anything about
excel. Is there a way to automate it all the time to show today's date?

From,
Nakia Allen

"Dave Peterson" wrote:

I'd apply data|filter|autofilter

Filter to show the rows that need to be deleted and then delete those visible
rows.

Acute Mind wrote:

I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

--

Dave Peterson

Dates and autofilter and code can sometimes get mixed up.

But this worked for me.
I put my dates in column F, headers in row 1 and used the format from the date
in F2. (adjust as necessary):

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim maxDate As Long

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
With .Range("f:f")
maxDate = CLng(Application.Max(.Cells))
.AutoFilter field:=1, _
Criteria1:=Format(maxDate, .Cells(2).NumberFormat)
End With
With .AutoFilter.Range
On Error Resume Next
.Resize(.Rows.Count - 1, 1) .Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
On Error GoTo 0
End With
.AutoFilterMode = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Acute Mind wrote:

Hello Mr. Peterson,

Is there a way to do the autofilter without having to literally choose the
date each time? I mean, is there a way to set the autofilter to today's date
all the time because if I get this working, my co-worker will be using it as
well on my day's off, and she doesn't understand filtering or anything about
excel. Is there a way to automate it all the time to show today's date?

From,
Nakia Allen

"Dave Peterson" wrote:

I'd apply data|filter|autofilter

Filter to show the rows that need to be deleted and then delete those visible
rows.

Acute Mind wrote:

I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

--

Dave Peterson


--

Dave Peterson

And if you want to always use today's date:

maxDate = CLng(Application.Max(.Cells))
becomes:
maxDate = CLng(date)

Dave Peterson wrote:

Dates and autofilter and code can sometimes get mixed up.

But this worked for me.
I put my dates in column F, headers in row 1 and used the format from the date
in F2. (adjust as necessary):

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim maxDate As Long

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
With .Range("f:f")
maxDate = CLng(Application.Max(.Cells))
.AutoFilter field:=1, _
Criteria1:=Format(maxDate, .Cells(2).NumberFormat)
End With
With .AutoFilter.Range
On Error Resume Next
.Resize(.Rows.Count - 1, 1) .Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
On Error GoTo 0
End With
.AutoFilterMode = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Acute Mind wrote:

Hello Mr. Peterson,

Is there a way to do the autofilter without having to literally choose the
date each time? I mean, is there a way to set the autofilter to today's date
all the time because if I get this working, my co-worker will be using it as
well on my day's off, and she doesn't understand filtering or anything about
excel. Is there a way to automate it all the time to show today's date?

From,
Nakia Allen

"Dave Peterson" wrote:

I'd apply data|filter|autofilter

Filter to show the rows that need to be deleted and then delete those visible
rows.

Acute Mind wrote:

I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

How would you use this to filter out and delete all records outside a given
date range, but making the date range variable? i.e. I want to be able to
select the date range at the start of the Macro, and then have the macro
filter out and delete all of the other records.

Currently I tried using:
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=28, Criteria1:= VARIABLE, Operator:=xlAnd

Where VARIABLE is set earlier.


"Dave Peterson" wrote:

And if you want to always use today's date:

maxDate = CLng(Application.Max(.Cells))
becomes:
maxDate = CLng(date)

Dave Peterson wrote:

Dates and autofilter and code can sometimes get mixed up.

But this worked for me.
I put my dates in column F, headers in row 1 and used the format from the date
in F2. (adjust as necessary):

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim maxDate As Long

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
With .Range("f:f")
maxDate = CLng(Application.Max(.Cells))
.AutoFilter field:=1, _
Criteria1:=Format(maxDate, .Cells(2).NumberFormat)
End With
With .AutoFilter.Range
On Error Resume Next
.Resize(.Rows.Count - 1, 1) .Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
On Error GoTo 0
End With
.AutoFilterMode = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Acute Mind wrote:

Hello Mr. Peterson,

Is there a way to do the autofilter without having to literally choose the
date each time? I mean, is there a way to set the autofilter to today's date
all the time because if I get this working, my co-worker will be using it as
well on my day's off, and she doesn't understand filtering or anything about
excel. Is there a way to automate it all the time to show today's date?

From,
Nakia Allen

"Dave Peterson" wrote:

I'd apply data|filter|autofilter

Filter to show the rows that need to be deleted and then delete those visible
rows.

Acute Mind wrote:

I am writing a macro to hasten a report I have to do everyday at work. The
final step is figuring out how to have Excel automatically delete all rows
containing today's date or the largest date in a particular column.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson