erase dates over 1 year
 

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.

erase dates over 1 year
 

Can we have a bit more information?

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

Mike

"mblhmlbrldll" wrote:

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.

erase dates over 1 year
 

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.
--
Gary''s Student - gsnu200806


"mblhmlbrldll" wrote:

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.

erase dates over 1 year
 

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.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200806


"mblhmlbrldll" wrote:

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.

erase dates over 1 year
 

Thanks!
--
Gary''s Student - gsnu200806


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200806


"mblhmlbrldll" wrote:

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.