Delete Rows Which Have Date 7 Days Old

**LSM1604** · 01-31-2010, 05:06 PM

Hi,

Thanks for the reply, sounds logical. But, exactly how do I do this? I am new to this stuff :S.

Thanks,

LSM

**Richard Buttrey** · 01-31-2010, 05:22 PM

Hi,

Assuming all your dates are in column A put a column label like 'Date' in A1 with your dates starting in A2, in B2 enter

=TODAY()-A2

and copy this down column B against all your dates.

Now put your cursor in A1 and from the Menu choose Data>Filter>AutoFilter
Click the drop down arrow in B1 and select 'Custom' and choose 'is greater than' and in the other drop down enter '7'

Now select all the filtered rows and delete them.

HTH

**LSM1604** · 01-31-2010, 05:27 PM

Hi,

Thank you for the prompt reply. Between me replying to the previous post and checking back to seeing that you had replied, I managed to work it out with a little VBA:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=""
    Range("A5").Select
    Do While ActiveCell <> ""
    If ActiveCell.Value < Now() - 7 Then
    ActiveCell.EntireRow.Delete
    ActiveCell.Offset(-1, 0).Activate
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
Range("A1").Select
ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
End Sub

Once again, thank you for all your help.

LSM

**Richard Buttrey** · 01-31-2010, 07:04 PM

Hi,

Well done and clearly that will work. However note that generally speaking, loops should only be used for relatively small ranges since there is a time overhead involved every time the VBE has to jump back to the Excel App, and vice versa. You'll find that with large ranges this can start to get time consuming. Always try and use standard Excel App. functionality since this will be quicker. In this case you could still have a macro, but instead of looping down a range just have it perform the Data Filter and delete rows functionality.

Regards

**LSM1604** · 01-31-2010, 07:29 PM

Hi,

Thank you for the advice, I will take the advice on board about the loops. I may have a play with data filters (I will have a search on the net).

Thanks again,

LSM