asked on

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

aikimark

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

Open in new window

RWayneH

ASKER

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?

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

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?

aikimark

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

RWayneH

ASKER

Thanks for the help.