Delete the entire row if the Date is older than six months

[KTSARA]

Hello Experts,
I have a worksheet with dates, email addresses and several other data. I am planning to send email notifications based on the date. After sending the notification, keeping that record more than six months is useless. So, what I want to do is to delete the entire row if the Date value is older than six moths, but I want to do this automatically without clicking on any command buttons. I found several codes from threads and tried to modify them, but they never worked as I wished. Sometimes, I might have placed the code in the wrong place as I am quite new to VBA and still learning. Below is the code I used and I placed it in the Sheet 7 code (I want to apply this code to sheet 7 only). If you experts can help me to correct the below code or come up with a better new code it would be a great help. I would be glad if you could explain your answer in the simplest way.

Option Explicit

Sub ClearOldData()

    Application.ScreenUpdating = False
    Dim i As Long
    Dim LastRow As Long

    With Sheets("Service Reminders")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row ' <-- get last row with data from column B

        For i = LastRow To 2 Step -1 ' always loop backwards when deleting cells/ranges/rows
            If DateDiff("d", .Range("B" & i).Value, Date) > 183 Then ' older than 183 days
                .Rows(i).Delete
            End If
        Next i
    End With
    Application.ScreenUpdating = True

End Sub

Thanks a lot in advance

 

[Michael M]

It depends on how you want to "trigger" the action of deleting rows....
You could use either a worksheet_change event, or a Worksheet_calculate event

 

[KTSARA]

It depends on how you want to "trigger" the action of deleting rows....
You could use either a worksheet_change event, or a Worksheet_calculate event

Hello,
Thanks for the quick reply. Can't I trigger it whenever I open the workbook?
Thanks

 

[Michael M]

If the code you provided works for you, simply change

Sub ClearOldData()

to

Sub WorkBook_Open()

And make sure it is in "ThisWorkbook" module

 

[KTSARA]

If the code you provided works for you, simply change

Sub ClearOldData()

to

Sub WorkBook_Open()

And make sure it is in "ThisWorkbook" module

Hello Michael,
I placed the code in ThisWorkbook module and it deletes only the date cell only. Can you please help me to modify this code, so that it deletes the entire row?

I have these formulas in the respective cells of the columns:

Column C: =VLOOKUP(A2,'[Sheet1]MACHINE REGISTRY'!$C$2:$M$20000,11,0)
Column D: = "Hello,"&"%0AThere is a upcoming machine service on" & " " & TEXT(B2,"dd/mm/yyyy") & " " &"at HFZ. Your presence is expected."
Column E: =HYPERLINK("mailto:"&C2&"?subject="&"Service Reminder"&"&cc="&"la.w@abc.com"&"&body="&D2,"SendNotification")

I don't know whether these formulas affect the code as well.

Thanks in advance