Delete entire row if older then 365 days

  • Thread starter Thread starter Pas
  • Start date Start date
P

Pas

How do I create a macro that will delete entire row if older then 365 days.
Dates are on col "A"
last col of data = "U"
Data is kept in sheet "details"

Thank you
 
If you agree to ignore lap years then the following simple code will do.
Put it into the Sheet "details" Level:
--------------------------------
Sub Del_Oldies()
LR = Cells(Rows.Count, 1).End(xlUp).Row
For R = LR To 1 Step -1
If Date - Cells(R, 1) > 365 Then Cells(R, 1).EntireRow.Delete
Next
End Sub
 
Many thanks מיכ×ל (מיקי) ×בידן

I did as suggested but get the following error message.

Compile error:
Variable not defined

any ideas?
 
sorry i also get "LR =" highlighted in blue

מיכ×ל (מיקי) ×בידן said:
If you agree to ignore lap years then the following simple code will do.
Put it into the Sheet "details" Level:
--------------------------------
Sub Del_Oldies()
LR = Cells(Rows.Count, 1).End(xlUp).Row
For R = LR To 1 Step -1
If Date - Cells(R, 1) > 365 Then Cells(R, 1).EntireRow.Delete
Next
End Sub
Click to expand...
 
Try;


Sub DeleteOldDates()
Dim lDate As Long

lDate = Date - 365
With Sheets("details")
.AutoFilterMode = False
.Range("A1:A2").AutoFilter Field:=1, Criteria1:="<" & lDate
.AutoFilter.Range.Offset(1,
0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With

End Sub
 
You may consider to delete the "Option Explicit" Statement otherwise you are
forced to declare all variables at that Module level.
OR:
Declare the variables R, and LR as Integers .
Check the VBE Help.
Micky


Used at module level to force explicit declaration of all variables in that
module.


--
והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"
 
Sorry guys,
I'm still getting error messages with both suggestions.
How do I declare the variables R, and LR as Integers .
 
Back
Top