Delete rows if older than current month

I have a workbook we are using as a schedule. First worksheet is the actual schedule, and once a project is completed, I have a macro that moves it to a "Completed" worksheet and deletes it from the schedule. Every month, we will create a copy and save the "Completed" worksheet as the month of completion. From there, I want to clear the "Completed" worksheet of everything from the previous month (i.e. Everything done in the month of April, I want it deleted so there will only be projects completed in the month of May). Currently this is what I have:

Please Login or Register  to view this content.

It somewhat works. The issue comes into play depending on the amount of days within a month. I don't care about the days, just which months they are in. V6 is the start of the range, and V100 should be a cell we never reach in a month. The second issue I am running into is, it usually takes two or more times of running it before it does delete the rows.

You might kick yourself after seeing this... LOL

Please Login or Register  to view this content.

Also take the year into consideration. Because if you are in November 2022 and you already have data for January 2023, the data of January 2023 will be deleted too.
So:

Please Login or Register  to view this content.

Or use entirerow.delete ofcourse

Originally Posted by JEC.

Also take the year into consideration. Because if you are in November 2022 and you already have data for January 2023, the data of January 2023 will be deleted too.
So:

Please Login or Register  to view this content.

Instead of adding all of that in red... why not add something as simple as (below in green):

Please Login or Register  to view this content.

I could be off JEC, I know you're a great VBA coder but this just seems like a simpler and more readable solution

Mine will delete the entire row but in all seriousness, JEC is a better VBA programmer than me lol

Originally Posted by Ian_12

Yes, yours does delete the whole row but I found a couple issues I can't resolve. Might also just need to close excel and see if it fixes it. So it still won't delete each row the first time. I have to click twice to clear everything. Next, it doesn't want to delete a row if it is from the previous year and a month greater than the current. So example, I put a date of 12/20/2021 and 4/30/2021. It would delete 4/30/2021, but not 12/20/2021.

Right, I didn't think about that. This code (below) will fix that:

What do you mean by clicking twice? Does a message box pop up?
This code will now delete rows before the year 2022 & disable all excel pop-ups

Please Login or Register  to view this content.

Originally Posted by Ian_12

Yes, yours does delete the whole row but I found a couple issues I can't resolve. Might also just need to close excel and see if it fixes it. So it still won't delete each row the first time. I have to click twice to clear everything. The next thing is, it doesn't want to delete a row if it is from the previous year and a month greater than the current. So example, I put a date of 12/20/2021 and 4/30/2021. It would delete 4/30/2021, but not 12/20/2021.

EDIT:

Here is what I have now. This is doing what I expect besides requiring several runs for it to delete all the rows.

Please Login or Register  to view this content.

Wait.... is your range more than V6->V100?? That's the only reason I can see it requiring to run multiple times.

Change your range to

Please Login or Register  to view this content.

By clicking I meant a button I have the macro assigned to. In order to delete all the rows that match the criteria, I have to use it at least twice or even more. But no, there shouldn't ever be any data beyond row 100. Just for some more insight, here is a shot of what I am working on

Attachment 781693

So V will be the completed date of the project. That column will dictate which rows need to be deleted.

Originally Posted by Ian_12

By clicking I meant a button I have the macro assigned to. In order to delete all the rows that match the criteria, I have to use it at least twice or even more. But no, there shouldn't ever be any data beyond row 100. Just for some more insight, here is a shot of what I am working on

Attachment 781693

So V will be the completed date of the project. That column will dictate which rows need to be deleted.

See the yellow ribbon on the top of the webpage for attaching files. The picture doesn't come thru when using the button in the quick reply screen.

Ah yes, forgot. Wasn't really thinking of posting the workbook. This is just the sheet I was dealing with. I left both macros in it and haven't tweaked them anymore. The dates in column V should be a good range of things to test out.

Important thing if you want to delete rows one by one:
Always go from bottom to top. That's the reason why you have to click twice in this case.

So here are two options: (first one deletes the specific rows at once)

Please Login or Register  to view this content.

Please Login or Register  to view this content.