Delete records using AutoFilter

I am using the following code to delete some records from a sheet.  How would I tell the filter to filter on anything that is older than 90 days? (in Ln7 and 8)  Please advise and thanks.

Sub DeleteOldRecords()
'PostSavedCOGIs  'Delete records older than 90 days.

    Sheets("PostSavedCOGIs").Select
    Rows("1:1").AutoFilter
    
        ActiveSheet.Range("A:U").AutoFilter Field:=21, Criteria1:= _
        "<11/11/2016", Operator:=xlAnd
    
    LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
    LastColumn = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Range(Range("A2"), Cells(LastRow, LastColumn)).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    
    Rows("1:1").Select
    Range("B1").Activate
    Selection.AutoFilter
    
    
End Sub

Open in new window

RWayneHAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aikimarkConnect With a Mentor Commented:
Oops.  I posted a seven day solution.  Please try this:
ActiveSheet.Range("A:U").AutoFilter Field:=21, Criteria1:= _
        "<" & dateadd("d",-90,date), Operator:=xlAnd

Open in new window

0
 
aikimarkCommented:
ActiveSheet.Range("A:U").AutoFilter Field:=21, Criteria1:= _
        "<" & dateadd("d",-7,date), Operator:=xlAnd

Open in new window

0
 
RWayneHAuthor Commented:
Would you mind explaining the code?   Where does it say 90 days?  I would like to use this in other places that the days older than 90 will chg to 60 or even 30..   Not understanding the -7?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RWayneHAuthor Commented:
Sorry I probably should have mentioned that the filter can be blank...  when it is blank it deletes the header row!!  Is there a way to alter the code to have it not delete that header row (Row1) if the filter returns nothing?
0
 
aikimarkCommented:
if nothing is showing but the header, then look at the number of visible rows (count) before you do the delete.  It is a conditional deletion.
if Range(Range("A2"), Cells(LastRow, LastColumn)).SpecialCells(xlCellTypeVisible).rows.count <>0 then
    'do the delete here
end if

Open in new window

0
 
RWayneHAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.