| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add one year to dates | Excel Worksheet Functions | |||
| how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
| Insert Dates for Year | Excel Discussion (Misc queries) | |||
| dates in year | Excel Worksheet Functions | |||
| How can I use day of year dates in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
