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

Dates and filtering and VBA don't always play nice...

selection.autoFilter Field:=28, Criteria1:="<"&clng(cdate(variablestr1)), _
Operator:=xlOr, Criteria2:=""&clng(cdate(variablestr2))

is where I'd start.

I figured the variables were strings that looked liked dates.

T Wiseman wrote:

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


--

Dave Peterson

Hi Dave,

Sorry about "piggybacking" this thread but I just noticed your comment
"Dates and filtering and VBA don't always play nice...". just curious
but why is this?

I do a lot of filtering on ageing inventory (cheese) & have created a
macro with a keyboard shortcut to allow me to filter faster than I can
by using the mouse & "custom filter" etc. I have found that when
filtering for dates the macro sometimes works & sometimes doesn't & am
unable to figure out what causes the differences b/n date/string
recognition. I'm using Excel 2002 both @ home & @ work; my home
computer has not yet failed to filter dates but the work one has. There
is no discernible difference (to me anyway) b/n the formats of cells
that work & cells that don't, or if the cell values are "real" dates
(eg dd/mm/yy) or "built" from referenced codes (eg = A1 & "/" & B1 &
"/" & C1).


I'm going to try your coding below when I get to work tomorrow &
hopefully it will solve any future issues.
Question 2: I use this macro for much more than just date filtering so
I'd like to be able to check if it is a date I'm filtering. How can I
check the formatting of the active cell & see if it is a date before
your line of code?

eg
Dim FilterValue As String
Dim FilterValueDate As Date
FilterValue = ActiveCell
If ActiveCell.Format = "m/d/yyyy" Then 'doesn't work
FilterValueDate = FilterValue
Else
End If
MsgBox ActiveCell.Format ' doesn't work

Thanks in advance,
Cheers
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


Dave Peterson Wrote:
Dates and filtering and VBA don't always play nice...

selection.autoFilter Field:=28,
Criteria1:="<"&clng(cdate(variablestr1)), _
Operator:=xlOr, Criteria2:=""&clng(cdate(variablestr2))

is where I'd start.

I figured the variables were strings that looked liked dates.

Dave Peterson


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=358973