Help with code to delete a row if todays date has passed

[nje]

Please take a lookat the attached worksheet
. I have looked through the forum at lots of codes to identify certain cells but can't find anything I'm capable of changing to suit what I want.
I have a booking form covering A5 to H13, The dates are in column E
I would like the code to delete evrything in that row where the date in that row has passed, if that is possible.
There will eventually be about 20 identical sheets, so I would appreciate it if you could also tell me whether to put the code in the sheet, workbook or module
Thank you

[MarvinP]

Hi nje,

Something like this?

Please Login or Register  to view this content.

[nje]

MarvinP - Thank you for your very fast response, although it did frighten me when I just used your code in my original workbook because it not only deleted the contents of the rows but also all the rows as well.
I probably didn't explain it very well, What I would like is for it to delete just the contents of rows 5 to 13 and leave the booking form intact other than that,
I forgot to mention that underneath the advance booking form, there will be a large booking sheet that is filled in on the day and it will show historical bookings so I need to leave everything underneath row 13 alone.

I put your code into a module, I hope that was the right place to put it. I also noticed that I had to use 'run macro' to get it to work, but would if possible like it to be automatic and be triggered from the date, but again, I've probably messed something up there.
Thanks again

[MarvinP]

Hi,

To make it stop at row 13 change

For RowCtr = LastRow To 1 Step -1
to
For RowCtr = LastRow To 13 Step -1

I guess it is time to for you to learn to follow code. Read http://www.cpearson.com/Excel/DebuggingVBA.aspx
Put a breakpoint in the code and step through it to see why it isn't working like you want.

Perhaps you have some blank cells in column E and those are being deleted too? Are your dates, really dates?

BTW - I didn't test my code on your sheet. I figured showing how to work through all the sheets might be a hint enough....

[nje]

MarvinP - Thanks for the help and advice, it isn't for the lack of searching and reading, I've just bought Macros for dummies and am working through that.
Thanks again

[nje]

I've read through the article you provided, and I've learnt about breakpoints and stepping through but if you don't what you are looking for, it doesn't really help and if I did know what I was looking for, then I wouldn't need the help.
My dates in column E are dates, and there are blanks in column E but I don't see what the connection between blank cells and the macro deleting all the rows instead of just the contents of thoses rows is, or why the macro doesn't run automatically. I would have appreciated it if you had just said you didn't want to help rather than, providing a macro that doesn't do what I wanted and then expect me to try and sort it out when I clearly don't have an understanding of it. I do however value the skills of those on this forum, but unfortunately, I can't match them
Nevertheless, thanks for reading the thread in the first place.

[MarvinP]

Hi,

Sorry for the problem. We never know how advanced people are or how much help they really need. Find the attached where I've put data in all 3 sheets and also included the macro that will go to all sheets and remove rows where the data is before today....

[nje]

MarvinP- Thanks for the extra effort, it can't be easy when you have to revert back to explaing the very basics. It works very well, but some questions if I can.
Where it says lastrow step 13-1, I thought that meant it would only work down to that row, but in your sample, it also works all the way down the sheet, if there is a date in it, which would be a problem.
Is there a way to run this automatically, for instance when you open the workbook,
and last but not least, when the macro runs through the sequence of deleting, it ends up on the last sheet where the last sheet stays open, is it possible to come back to the sheet where it started from.
Thank you

[MarvinP]

Hi,

The lastrow variable will be the last used row on a sheet. This number will be GREATER than 13. Then in the For Next loop the Step of "minus 1" will start at this big number and work up to row 13 before it stops working. The Step - 1 makes it work from the bottom to the top.

To get back to sheet1 put a statement of
Worksheets(1).Select
just before the Sub End statement in the code.

To make this work automatically (I don't recommend this) you could put some Event Code behind the Workbook_Open that would call this Sub...
see http://www.cpearson.com/excel/Events.aspx


I hope this helps.

[nje]

MarvinP- Thanks for the extra effort, it can't be easy when you have to revert back to explaing the very basics. It works very well, but some questions if I can.
Where it says lastrow step 13-1, I thought that meant it would only work down to that row, but in your sample, it also works all the way down the sheet, if there is a date in it, which would be a problem.
Is there a way to run this automatically, for instance when you open the workbook,
and last but not least, when the macro runs through the sequence of deleting, it ends up on the last sheet where the last sheet stays open, is it possible to come back to the sheet where it started from.
Thank you

Sorry don't know how this got on here twice

[nje]

MavinP- Great work, I'll bow to your knowledge on this and instead of trying to automate it, I have decided to put a button on the Master Sheet which can be pressed to clear the old dates each day before looking at the sheets.
The little fix Worksheets(1).select work really well. Thanks
I have read what you have said, about working from bottom to top and it's causing me problems because the sheet will be full of older dates, it's a charity and by law they have to keep historical records.
I only want to delete those rows that include row 4 through to row 13 not any others, Is this possible, otherwise all your effort will be wasted because otherwise it will delete valuable information which is needed.
Thanks

[MarvinP]

Hi nje,

It seems to me the way to only work on rows 4 to 13 would be to change the code to

For RowCtr = 13 to 4 Step -1

[nje]

Youv'e cracked it, spot on. Many thanks for your help and advice