View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Dave is offline
external usenet poster
 
Posts: 1,388
Default Auto delete a row after 5 weeks in Excel

Hi,
If your dates are in Column C, and your data starts in row 2, try this small
macro:

Sub DeleteOld()
A = 0
Do Until Range("C2").Offset(A, 0) = ""
If Range("C2").Offset(A, 0) < Date - 35 _
Then Range("C2").Offset(A, 0).EntireRow.ClearContents
A = A + 1
Loop
End Sub

If your dates start in some other cell, change the 3 instances of C3 to the
first date cell reference.

Right click the sheet tab, select View Code, paste the above into the VBA
window.
You can use a button to fire the macro, which will delete each entry that's
passed its use-by date.

If you want this to happen automatically, you'll need to change the code
slightly and put it somewhere else. Let me know.

Note: This deletes the whole row, which makes it simple to write code, but
undesirable if you have other info in columns to the right.

Regards - Dave.