Delete Rows Which Have Date 7 Days Old

[LSM1604]

Hi,

I was wondering if somebody could help me with a little bit of code?

When the sheet is opened, I would like some code to search down column A, starting from cell "A5" and delete all rows which contain a date which is greater than 7 days ago.

The dates are in order from the most recent being at the top.

For example:
Today is 29/01/2010, I would like all rows which have a cell in column A containing Today-7() to be deleted. 28/01/2010 to stay. 19/01/2010, delete row.


Hope that makes sense?

Cheers,

LSM

*cross post @ http://www.mrexcel.com/forum/showthread.php?t=444663*

[Richard Buttrey]

Hi,

No need for VBA. Why not just use a helper column with the formula =TODAY()-A5, format it as a number and after copying it down your range filter this column for >7 then delete all the filtered rows.

(You could of course record a macro to do all that and achieve the same thing)

HTH

[LSM1604]

Hi,

Thanks for the reply, sounds logical. But, exactly how do I do this? I am new to this stuff :S.

Thanks,

LSM

[Richard Buttrey]

Hi,

Assuming all your dates are in column A put a column label like 'Date' in A1 with your dates starting in A2, in B2 enter

Please Login or Register  to view this content.

and copy this down column B against all your dates.

Now put your cursor in A1 and from the Menu choose Data>Filter>AutoFilter
Click the drop down arrow in B1 and select 'Custom' and choose 'is greater than' and in the other drop down enter '7'

Now select all the filtered rows and delete them.

HTH

[LSM1604]

Hi,

Thank you for the prompt reply. Between me replying to the previous post and checking back to seeing that you had replied, I managed to work it out with a little VBA:

Please Login or Register  to view this content.

Once again, thank you for all your help.

LSM

[Richard Buttrey]

Hi,

Well done and clearly that will work. However note that generally speaking, loops should only be used for relatively small ranges since there is a time overhead involved every time the VBE has to jump back to the Excel App, and vice versa. You'll find that with large ranges this can start to get time consuming. Always try and use standard Excel App. functionality since this will be quicker. In this case you could still have a macro, but instead of looping down a range just have it perform the Data Filter and delete rows functionality.

Regards

[LSM1604]

Hi,

Thank you for the advice, I will take the advice on board about the loops. I may have a play with data filters (I will have a search on the net).

Thanks again,

LSM

[xainshah]

Can you please more elaborate this, how did u use this VBA as this is not working on my sheet?

[Richard Buttrey]

Hi, and welcome to the forum.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too. And since your workbook is going to be different then you will need to provide us with it - in a new thread not here!