erase dates over 1 year

  • Thread starter Thread starter mblhmlbrldll
  • Start date Start date
M

mblhmlbrldll

I use a spreadsheet to keep track of information. each piece of information
has a cell next to it with the date it was entered. I want the program to
automatically delete any information that is more than 1 year old.
 
Can we have a bit more information?

Where is the date?
what do you want deleting, the entire row?

Mike
 
If the dates are in column B, then run:

Sub oldated()
n = Cells(Rows.Count, "B").End(xlUp).Row
cdt = Date - 365
For i = n To 1 Step -1
If Cells(i, 2).Value < cdt Then
Cells(i, 2).EntireRow.Delete
End If
Next
End Sub


So if we start with data in cols A & B like:

data1 1/22/2008
data2 4/4/2007
data3 5/17/2007
data4 6/10/2008
data5 10/26/2007
data6 12/11/2007
data7 2/19/2007
data8 10/6/2007
data9 11/24/2007
data10 7/7/2007
data11 1/19/2007
data12 6/7/2008
data13 7/28/2008
data14 5/20/2008
data15 12/24/2007
data16 9/26/2008
data17 3/5/2008
data18 12/12/2006
data19 1/25/2008
data20 2/27/2007
data21 6/18/2007
data22 6/13/2007
data23 2/12/2007
data24 10/10/2008
data25 9/27/2008
data26 1/22/2007
data27 3/2/2008
data28 6/5/2008
data29 3/18/2007
data30 5/17/2007
data31 11/17/2006

the macro will remove old dates and leave:

data1 1/22/2008
data4 6/10/2008
data5 10/26/2007
data6 12/11/2007
data9 11/24/2007
data12 6/7/2008
data13 7/28/2008
data14 5/20/2008
data15 12/24/2007
data16 9/26/2008
data17 3/5/2008
data19 1/25/2008
data24 10/10/2008
data25 9/27/2008
data27 3/2/2008
data28 6/5/2008

Change the date column to suit your needs.
 
Instead of this line of code....

Cells(i, 2).EntireRow.Delete

you can use this simpler line of code...

Rows(i).Delete

and it should work the same.
 
Thanks!
--
Gary''s Student - gsnu200806


Rick Rothstein said:
Instead of this line of code....

Cells(i, 2).EntireRow.Delete

you can use this simpler line of code...

Rows(i).Delete

and it should work the same.
Click to expand...
 
Back
Top