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