Delete Rows Over 3 Months Old as of Today (VBA)

C45P4R

New Member
Joined
Oct 15, 2014
Messages
39
Hi all. I decided to start making macros at work for a few of our processes. I want to learn more about Excel. At the start of this project I had no VBA knowledge what-so-ever. I now have almost none :LOL:. So far I've based a lot of the code from another macro and with the rest from Google searches and trial and error. Anyway, I'm stuck!

I need to delete all data which is over 2 months old from current date. I found a suggestion from another thread, but can't get it to work. To be honest, it's hard to work with because I don't really know what a lot of it means...

Sub
With Sheets("Booked")
LR = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = LR To 2 Step -1
If .Cells(i, "AE").Value < DateAdd("m", -2, Date) Then
.Rows(i).EntireRow.Delete
End If
Next i
End With
End Sub

It seems to just cycle through the stages again and again without actually deleting anything.

I also need to label the data in a separate column based on the date relative to the day the macro is run. I can't find how to do this either. Any help would be much appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Without looking at too much of your code, I rewrote it just to see the differences.

Code:
Sub deleteRows()

    With Sheets("Booked")
             'This assumes your dates are in "AE"
        For x = .Cells(Rows.Count, "AE").End(xlUp).row To 2 Step -1
            If .Cells(x, "AE").Value < DateAdd("m", -2, Date) Then
                .Cells(x, "AE").EntireRow.Delete
            End If
        Next x
    End With


End Sub

So your code makes sense. Here's my question, is your data actually in column AE?
 
Upvote 0
Can you describe the worksheet format and what you need deleted? Most important will be where the date is, but the more detail the better.
 
Upvote 0
Neon's code look like it will do the first piece you asked just fine (Assuming date in "AE"). It is essentially just finding the last row and looping through each row backwards (why backwards, ask the OP) and then checking the date. if the date is < the current date - 2 months, it deletes the row. easy peasy.
 
Upvote 0
Neon's code look like it will do the first piece you asked just fine (Assuming date in "AE"). It is essentially just finding the last row and looping through each row backwards (why backwards, ask the OP) and then checking the date. if the date is < the current date - 2 months, it deletes the row. easy peasy.

It loops backwards because it's deleting rows. If it was going from 1 to 10...

Row 1 - Not deleted (x = 1)
Row 2 - Not deleted (x = 2)
Row 3 - Deleted (x = 3)
Old Row 4 New Row 3 - {This becomes the new Row 3 and is not evaluated because x = 4}
Old Row 5 New Row 4 - Not deleted (x = 4)
 
Upvote 0
Ah, I know exactly what you mean. I've dealt with this problem other ways in the past, but that's another way of doing it. Cool.
 
Upvote 0
As NRS has commented your existing code will work if you have a date in column AE..... with the proviso that it is a date serial !!! Ie date serial rather than a text string?

If by any chance your dates are text then you can change the line of code that tests the date to be....

Code:
If CDate(.Cells(i, "AE").Value) < DateAdd("m", -2, Date) Then

That should even be good for a mixture of text and serial dates.

Hope that helps.
 
Upvote 0
It loops backwards because it's deleting rows. If it was going from 1 to 10...

Row 1 - Not deleted (x = 1)
Row 2 - Not deleted (x = 2)
Row 3 - Deleted (x = 3)
Old Row 4 New Row 3 - {This becomes the new Row 3 and is not evaluated because x = 4}
Old Row 5 New Row 4 - Not deleted (x = 4)

I wanna be on this level! I love this software, can't wait to get better. Thanks for the replies, I'll give it a go.

Pdevito - The date I want this judgement based on is in column AE. If the date is then over 2 months prior to todays date, I want to delete the entire row. If not, all the data and then shift up the row.

Also, can't see a way to edit on this forum, but I didn't mean 3 months like it says in my title, I meant 2 lol. Seems you guys did it the way I needed anyway :P.
 
Upvote 0
As NRS has commented your existing code will work if you have a date in column AE..... with the proviso that it is a date serial !!! Ie date serial rather than a text string?

If by any chance your dates are text then you can change the line of code that tests the date to be....

Code:
If CDate(.Cells(i, "AE").Value) < DateAdd("m", -2, Date) Then

That should even be good for a mixture of text and serial dates.

Hope that helps.

You may be onto something... It's set as general. Actually going to get round to trying these things now. Thanks.
 
Upvote 0
'General' should normally be a suitable format for serial date input unless you have imported some text dates?

I have just noticed the last line of your original post which if I understand correctly means that you wish to record, against the non-deleted rows, the date that you last ran the macro???

If so then try......

Code:
Sub Delete_Rows ()
With Sheets("Booked")
LR = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = LR To 2 Step -1
If CDate(.Cells(i, "AE").Value) < DateAdd("m", -2, Date) Then
.Rows(i).EntireRow.Delete
Else
.Cells(i, "AG").Value = Format(Date, "dd-mm-yy") ' *****  Edit column and date format to suit
End If
Next i
End With
End Sub

Will record the run date in AG Edit to suit!!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top