Delete entire row if older then 365 days

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
0
Utf
4/3/2010 10:31:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
696 Views

Similar Articles

[PageSpeed] 4

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
0
Utf
4/3/2010 11:04:01 PM
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
0
Utf
4/4/2010 1:01:01 AM
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
0
Utf
4/4/2010 1:03:01 AM
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@discussions.microsoft.com> wrote in message 
news:1A05E0D7-7EFD-45FE-9907-8E69987FB5CD@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 

0
ozgrid
4/4/2010 3:03:52 AM
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"


"Pas" wrote:

> 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
0
Utf
4/4/2010 7:11:01 AM
Sorry guys,
I'm still getting error messages with both suggestions.
How do I declare the variables R, and LR as Integers .


"ozgrid.com" wrote:

> 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@discussions.microsoft.com> wrote in message 
> news:1A05E0D7-7EFD-45FE-9907-8E69987FB5CD@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 
> 
0
Utf
4/4/2010 9:13:01 PM
Reply:

Similar Artilces:

Mutual Fund Last Update and Day's Change not updating
THIS PRODUCT IS MICROSOFT!!! IT IS BILL GATES!!! How long will the developers allow this problem to go on??? I've used MSN Money's Portfolio for years and just started getting the problem in the past six months. A post from MS says "we are working with our data providers to supply the correct date". Fire the data provider and go back to the one you used for years. I think this is a MS programming error that should be fixed by an entry level programmer in two hours. I'll take that bet. "evy38" <evy38@discussions.microsoft.com> wrote in messa...

Deleting a User from CRM 08-29-06
HI I want to delete a User from CRM. How do I go about doing this? Could you please help me? What I had done is, I deleted the User from Active Directory Users But still the User is shown in Microsoft CRM Deployment Manager- User Manager List. Hi, as far as I know you can not delete a user with any means of the CRM. The only option is to deactivate the user. However, you can try to delete the user directly on the database. Best regards, Stefan Sachs "rajat kalia" wrote: > HI > I want to delete a User from CRM. How do I go about doing this? Could you > please hel...

Messages Deleted When Replying
When replying to an email, the email is being automatically deleted and does not end up in any other folder. The only way to see the original email message is to go to Sent Items and follow the thread. I've run a full system scan and cannot find any viruses. Any ideas? Thank you. Check your view. Make sure it's not set to unread message. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealo...

Deleted email account pops up after closing and restarting Live Ma
Since I migrated over my email accounts from Windows Mail to Windows Live Mail, I have noticed that one of my gmail accounts (I have more than 2) started showing up twice on the left sidebar. However, one of them didn't show folders when i clicked on it and asked to have the folders downloaded, despite all those folders already having been synced on my "working" email account. The first thing I did was delete the duplicate account from the "Tools" menu and it disappeared. All the other email accounts (live, gmail, & school seemed fine). However, on...

previous day
I have a query that is pulling from a table the previous days data using date()-1. I have addressed the problem if I'm running this query on a Monday then the table's last days data would be Friday (the last buisness day) so date()-1 would not work so I came up with this. IIf(Weekday(Date())=2,Date()-3,Date()-1) However, I thought what if there is a holiday like a 3 day weekend where my company has Monday off so I come in on Tuesday looking to pull the prevous days data. Well the previous data would be Friday (not Monday since we had the day off) so my iif statement would not...

Deleting multiple pages
From the edit menu, I can delete one page. Is there any way to delete a range of pages from a document? Thanks, RA RestlessAde wrote: > From the edit menu, I can delete one page. Is there any way to delete a range > of pages from a document? > > Thanks, > RA RestlessAde wrote: > From the edit menu, I can delete one page. Is there any way to delete a range > of pages from a document? > > Thanks, > RA RestlessAde wrote: > From the edit menu, I can delete one page. Is there any way to delete a range > of pages from a document? > > Thanks, &...

Outlook 2003 corrupted Task Reminder won't delete; options?
I'm running Outlook 2003 on a Vista PC. I have a Task Reminder that keeps coming up that I can't delete. I get the following message: "Cannot open the item for this reminder. Cannot locate recurrence information for this appointment. Data has become corrupted." How can I remove this reminder? Have you attempted to delete the task altogether and recreate it? "Ed Keeter" wrote: > I'm running Outlook 2003 on a Vista PC. I have a Task Reminder that keeps > coming up that I can't delete. I get the following message: > > &q...

Transpose Column With Gaps to Row With No Gaps?
How could I convert, by click and dragging a formula, the following? It's additionally tricky because the Horizontal format includes an end of year lable (2004, 2005, etc), but the vertical format does not Label Cell Reference 4Q04 R17 1Q05 R20 2Q05 R23 3Q05 R26 4Q05 R29 1Q06 R32 Label Cell Reference 4Q04 AG8 2004 AH8 1Q05 AI8 2Q05 AJ8 3Q05 AK8 4Q05 AL8 2005 AM8 1Q06 AN8 Thanks very much in advance. This will become a forecasting tool for debt, reported interest expense, accrued interest expense, capitalized interest expense, and cash inter...

Shortcut Key for delete?
Is there any keyboard shortcut for deleting a row? Instead of right click delete. -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32920 View this thread: http://www.excelforum.com/showthread.php?threadid=540175 Hi, You can select the row and hold down Ctrl and press - Regards, Bondi Thank you ! :cool: -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php?action=getinfo&useri...

How do I stop my e mails being automatically deleted?
The e mails in my out tray are being deleted shortly after I send them. How do i stop this. The system only started doing this a few weeks ago and I have lost a couple of years worth of sent e mails which is very very inconvienient, to say the least. My irritation with this random change in the system is intense. I have tried for a couple of hours to find out waht is going on using the automatic help system and have found nothing useful. Cheers bob Are you sure they are being deleted? Could you have a view with a folder hiding them? Are you syncing with a handeld device? -- Diane ...

Deleting elements from xml doc.
Hello, I am stuck trying to apply the following logic to the below xml: <?xml version='1.0' encoding='ISO-8859-1'?> <Collection> <Book Id='1' Locator='Yes'> <Title>Principle of Relativity</Title> <Author>Albert Einstein</Author> <Genre>Physics</Genre> </Book> <Book Id='2'> <Title>Cosmos</Title> <Author>Carl Sagan</Author> <Genre>Cosmology</Genre> </Book> </Collection> Read the "Locator&qu...

Sum to exclude blank row
Hi, I have a problem with trying to implement a summation formula. I have monthly data in columns (A to L for each month) and rows being the data. What I'd like to do is sum each month but exclude the value which is in a row of data that doesn't have a full 12 months of data. So basically I only want to SUM those sets of data which have a full 12 months. Is there a way to do this? Any help greatly appreciated. Elijah One approach: Assume your table is in A1:L6, headers in row1, data in row2 down Put in M2: =COUNT(A2:L2) Copy M2 down to M6 Col M will be used as the "crite...

Remove or delete teams
A team that is accidentally added or added incorrectly (wrong business unit) should be able to be deleted. It is understandable that from a business perspective it may be undesirable to remove a team that has been connected to members, services, or other data types; but a team that has just been added should be able to be removed. Moreso, since this is a simple feature to implement (simply removing the selected row from the TeamBase table of the MSCRM database does the trick to my knowledge) it should be selected for implementation without a huge amount of customer demand. ----------...

Retrieve deleted excel document
Is there a way to retrieve a spreadsheet that have been overwritten? Thanks Johnson, David wrote... >Is there a way to retrieve a spreadsheet that have been overwritten? Only from backups, so I guess the short answer is no. David, not if it has been saved, if not saved just close the workbook and open it back up, if it has been saved do you have a backup? maybe on a disk, copy on an e-mail, backed up on a network server.... -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is al...

Error When deleting from CRM 3.0
All of a sudden I cannot delete any data from CRM 3.0. I recieve an error that states "SQL Server Error Happened. Please contact an administrator" I cannot find any information in the Event viewer. Has anyone seen this problem or know of a solution. Any help is greatly appreciated. Thanks, Lee I have equal problem. already does someone have a solution for it? Thanks... "Lee" schrieb: > All of a sudden I cannot delete any data from CRM 3.0. I recieve an error > that states "SQL Server Error Happened. Please contact an administrator" I > c...

How do I number rows automatically?
I want to add an automatic (ie self-changing) numbering column to worksheet. I would like the left most column to have anumer if th next column to the right has text, and then add 1 number to that colum for each subsequent row. An eg: 1. Joe Blow 123 Main st 2. Sara Silver, 221 Green Ave 3. Fred Red, 444 Bloacl Blvd. where if I add text to the next 1st name column, the next number i automatically fillled or if I delete the row with Sara Silver, then th next ro read: 2. Fred Red etc I have tried using the formulas and the help, but to no avail. Thanks for your assistance. A -- Message poste...

problem deleting email
OK, here it is as simple as I can make it. I have a problem that requires the Inbox Repair Tool. Now, how do I get this tool and will it fix the problem that I have. I cannot delete any emails. tried following the way that the puter said to get to the repair tool, but that isn't the way. stuck here. help me please! ...

Deleted Items #8
Is there a way to set Outlook to delete items from the deleted items folder upon exiting WITHOUT having to go to each computer? On 19 Sep 2006 12:44:41 -0700, "chad" <slidellinternational@gmail.com> wrote: >Is there a way to set Outlook to delete items from the deleted items >folder upon exiting WITHOUT having to go to each computer? Group Policy with the outlook adm will probably let you do that. Which you get by downloading the Office Resource Kit, I believe... -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburn...

Runtime Table Deletes Problem
Hi, I run an Access 2003 system where the reference data and user data are kept in 2 separate DB's, the ref data is updated monthly and issued to the users. The user db is updated with the new ref data by linking tables and then running delete/update queries. I have discovered this method has stopped working for some users lately, the previous months data is left intact, the users don't get an error message of any type, and I can't figure out why. (It also affects certain users when importing data to a temporary table which should be cleared before each new import). It st...

07 corrupt file; delete when compacting
I have a client that alerted me to a problem they have encountered several times. I logged in and rant he same process and got the same result. Thankfully, I made a copy of the data file (back end) before testing. On their server resides the back end data file at S:\SomeDir\SomeDataFile.MDB (they have Office 07 in the office but retain an 03 extension for the databases because too many laptops are still on 03). Once per week, when everybody has logged out of the system, a person opens the data file and performs a compact and repair. Recently, as in the last few months or so, the co...

Prevent file delete no password
Hi, I was wondering if it was possible to protect a document from bein deleted without making it required to have a password to be opened? I'm trying to fix some files I've made and save to a network drive however, I don't want anybody "accidentally" deleting them and the still have to be accessible without a password. Thanks so much in advance Bdun -- bdun ----------------------------------------------------------------------- bdunk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=50 View this thread: http://www.excelforum.com/showthread...

data validation of range, email specific row
Hi I'm trying to validate the data in cells C5:D5 if boths cells are yes, I would like it to send the row with both yes values and then set E5 to yes c d e tested? success? emailed? I don't know how to validate a range, I only figured out how to do one cell.. Sub Yes() Sheets("Sheet1").Select If Range("C5").Value = "Yes" Then MsgBox "Yes." End If End Sub How do I tell it to send the email after How do get it to send just the heading row and the row with c5:d5 value = yes as a html table? How do I set...

How can I crop the unwanted rows of a worksheet please.
Ineed to crop the bottom of a worksheet or how do I only print the area with information on. Thanks in advance Rory. You can set the print area to display only what you want. Select the cells you want in your print area and use the commands FILE -> SET PRINT AREA "RORY" <RORY@discussions.microsoft.com> wrote in message news:D9315BEC-B0E5-424C-8589-10B0C3F0978E@microsoft.com... > Ineed to crop the bottom of a worksheet or how do I only print the area > with > information on. > Thanks in advance > Rory. If you hit ctrl-end, does that take you way pa...

can't delete junk e-mails
i can't delete my junk e-mail. my e-mail says that i have 819 e-mails and when i go to them none of them show up and thats when "the unknon message has occured" pops up and all junk e-mails are going to my inbox instead of goinh thry my filter help someone please Upgrade to Windows Live Mail: http://download.live.com/wlmail=20 --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "mavapnar" <mavapnar@discussions.microsoft.com> wrote in message = news:9BFD3BF7-38D7-402F-82D4-56D5946B8D...

odd or even rows......and importing from Access
Can't get Excel to accept data in odd or even rows........from outside database. Access has one line (row of data) and that can be copied into Excel, however I would like to "split" some of that data between odd and even rows. I can create queries to separate the data on right or left from Access - example: Person has one name but has two arms legs and ears. I want the first colums in every row to contain the same name, but future colums in those two rows to contain either right(odd) or left(even side information. ie; row 1 Jones right ear good bad undetermined row 2 Jone...