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

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

#1. Your guess is as good as mine why they don't play nice. I remember one
time, I had to change the data's format to General, then filter using the serial
date number.

Sometimes (once??), I picked up the format from the first cell in that column
and used that to filter. The data all had the same format, so I was happy.

#2. VBA has its own =isdate() function that you could use.

broro183 wrote:

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

--

Dave Peterson

Hi Dave,

Thanks very much for the suggestion.
I used the "isdate" function as below, if the cell is not visible as a
string I have used the serial # & v.v.
There is probably a tidier way of presenting it, but I'm just learning
(so your guess is a lot better than mine!) & it seems to work so
far...


Code:
--------------------

'Checks if current cell is a date _
& shows FilterValue of current cell as date or string
If IsDate(ActiveCell) Then
AsSerial:
CurrentCellType = "Serial"
FilterValue = CLng(CDate(ActiveCell))
Else
AsString:
CurrentCellType = "String"
FilterValue = ActiveCell
End If
Selection.AutoFilter Field:=ColToFilter, Criteria1:="=" & FilterValue, Operator:=xlOr, _
Criteria2:="=*" & FilterValue & "*"
If ActiveCell.EntireRow.Hidden Then
Select Case CurrentCellType
Case Is = "Serial"
GoTo AsString:
Case Is = "String"
GoTo AsSerial:
End Select
Else
End If

--------------------


btw, this is just part of a larger macro which is why the else is
empty.

Thanks for your help,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


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

Working with those dates and filters is a pain for everyone (I'd bet). Glad you
got something working.

broro183 wrote:

Hi Dave,

Thanks very much for the suggestion.
I used the "isdate" function as below, if the cell is not visible as a
string I have used the serial # & v.v.
There is probably a tidier way of presenting it, but I'm just learning
(so your guess is a lot better than mine!) & it seems to work so
far...

Code:
--------------------

'Checks if current cell is a date _
& shows FilterValue of current cell as date or string
If IsDate(ActiveCell) Then
AsSerial:
CurrentCellType = "Serial"
FilterValue = CLng(CDate(ActiveCell))
Else
AsString:
CurrentCellType = "String"
FilterValue = ActiveCell
End If
Selection.AutoFilter Field:=ColToFilter, Criteria1:="=" & FilterValue, Operator:=xlOr, _
Criteria2:="=*" & FilterValue & "*"
If ActiveCell.EntireRow.Hidden Then
Select Case CurrentCellType
Case Is = "Serial"
GoTo AsString:
Case Is = "String"
GoTo AsSerial:
End Select
Else
End If

--------------------

btw, this is just part of a larger macro which is why the else is
empty.

Thanks for your help,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

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

--

Dave Peterson