Prev: hide formula in cells till data is available for calc
Next: Dropdown List That Accepts Other User Input
From: Pas on 3 Apr 2010 18:31
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
From: מיכאל (מיקי) אבידן on 3 Apr 2010 19:04
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
--------------
Micky


"Pas" wrote:

> 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
From: Pas on 3 Apr 2010 21:01
Many thanks מיכאל (מיקי) אבידן

I did as suggested but get the following error message.

Compile error:
Variable not defined

any ideas?

"מיכאל (מיקי) אבידן" wrote:

> 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
> --------------
> Micky
>
>
> "Pas" wrote:
>
> > 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
From: Pas on 3 Apr 2010 21:03
sorry i also get "LR =" highlighted in blue

"מיכאל (מיקי) אבידן" wrote:

> 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
> --------------
> Micky
>
>
> "Pas" wrote:
>
> > 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
From: ozgrid.com on 3 Apr 2010 23:03
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



--
Regards
Dave Hawley
www.ozgrid.com
"Pas" <Pas(a)discussions.microsoft.com> wrote in message
news:1A05E0D7-7EFD-45FE-9907-8E69987FB5CD(a)microsoft.com...
> 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

 |  Next  |  Last
Pages: 1 2
Prev: hide formula in cells till data is available for calc
Next: Dropdown List That Accepts Other User Input