How do I automate the deletion of a row containing today's date i.

[Acute Mind]

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]

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

[Acute Mind]

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]

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.Delete
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]

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.Delete
> 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

[T Wiseman]

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.Delete
> > 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]

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.Delete
> > > 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

[broro183]

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...

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

[Dave Peterson]

#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

[broro183]

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...

Please Login or Register  to view this content.

btw, this is just part of a larger macro which is why my variables aren't defined etc.

Thanks for your help,

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

[Dave Peterson]

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