Forum Discussion
VBA code to identify dates and clear rows
HansVogelaar​ has given you the code you asked for.
I want to take a different tack altogether, and ask you "WHY?"
Why do you want to wipe out history? What does that gain you? Have you thought of what you might be losing in the process?
Now, I'll grant you that most of the time you probably don't care about events or people involved in those events when more than five years has elapsed since the event. However, might there come a time when you would need or want to know? I ask this as the former (I'm now retired for over twenty years) director of a major database for a major corporation. We recorded all kinds of transactions -- i.e., events -- in the database I managed. Most of the time we really only cared about the current or most recent changes. But we never erased the history.
It doesn't really cost you anything (I would submit) to hold on to the history; yes, you'll have rows in there that are old, but unless there's some compelling reason to erase them, I strongly recommend that you keep them. Just in case.
For example:
Sub DeleteOldRows()
Dim FiveYearsAgo As Date
Dim tbl As ListObject
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
FiveYearsAgo = DateAdd("yyyy", -5, Date)
' Substitute the real table name
Set tbl = ActiveSheet.ListObjects("Table1")
m = tbl.ListRows.Count
For r = m To 1 Step -1
If tbl.DataBodyRange(r, 3).Value < FiveYearsAgo Then
tbl.ListRows(r).Delete
End If
Next r
Application.ScreenUpdating = True
End Sub
Thank you