Forum Discussion
VBA code to identify dates and clear rows
I have a three column table in my spreadsheet, I have a user form that inputs information into the table columns, a name, an event type and a date. That is then counted in another table and it tells me various totals etc. What I want to do is create a macro that will check the date column in the table for any dates more than 5 years ago and then clear the three cells in that row in the table if it's that old. Can someone provide the code for that as I have tried to copy some things from videos online but it's always something slightly different and I don't known how to adjust it because I don't know how to code.
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.
4 Replies
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.
Since posting this I too have been wondering if it's worth the effort. Ultimately my main driver was that I didn't want the spreadsheet to clog up going through rows and rows of data to extract the totals I'm looking for which are only relevant within the last three years. Now that I have a suggested solution, I can see how it goes before making a final decision.
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 SubThank you
