How to Delete or Clear Excel Entries Older Than 3 Months with a Macro
Question details
The user wants to automate the removal of spreadsheet entries older than three months using a VBA macro and a clickable button, keeping recent data intact.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing large datasets, specifically expense records, by cleaning up old historical entries to maintain a current view without manual deletion.
- Observed behavior
- The user needs a functional VBA script that correctly identifies dates older than three months, clears or deletes the corresponding rows, and triggers via a form button.
Always save a backup copy of your workbook before running macros that delete data, as VBA actions usually cannot be undone using the standard Undo feature. Ensure the Developer tab is enabled in your Excel ribbon.
Create and Run a VBA Macro to Remove Old Entries
This solution provides a complete VBA script to loop through dates in a specific column and remove rows containing dates older than three months.
When deleting rows dynamically in VBA, it is crucial to loop backwards (from the last row up) so that row indices do not shift incorrectly and skip entries. You can choose to either clear the contents of the row or delete the row entirely depending on your layout needs.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' in the top menu and select 'Module' to create a blank script window.
Copy and paste the following code into the module: Sub ClearOldEntries() Dim ws As Worksheet Dim lastRow As Long Dim checkDate As Date Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") checkDate = DateAdd("m", -3, Date) lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = lastRow To 2 Step -1 If IsDate(ws.Cells(i, "A").Value) Then If ws.Cells(i, "A").Value < checkDate Then ws.Rows(i).ClearContents End If End If Next i End Sub
Save your work and close the VBA editor to return to your Excel worksheet.

Assign the Macro to a Form Button
Adding a button to your worksheet allows users to run the cleanup macro with a single click without having to open the VBA editor.
Automate Spreadsheets Easily with WPS Office
WPS Office fully supports Excel VBA macros and form controls. You can seamlessly run, edit, and create advanced VBA scripts for data automation just like you do in Microsoft Excel.
- 1. Open your file: Launch WPS Spreadsheets and open your .xlsm or .xlsx file containing the expense data.
- 2. Enable Macros: Navigate to the 'Developer' tab and ensure macros are enabled for the session.
- 3. Open VBA Editor: Click 'Visual Basic' or press ALT + F11 to insert or edit your macro scripts.
- 4. Run Data Cleanup: Run your macro or click the assigned form button to easily manage and clear old entries.

Frequently Asked Questions
Can I undo a macro after it deletes data?
No, actions performed by a VBA macro typically clear the Excel Undo history. Always save a copy or backup of your workbook before running a deletion macro.
How do I change the macro to delete data older than 6 months instead of 3?
In the VBA code, locate the line `checkDate = DateAdd("m", -3, Date)` and change the `-3` to `-6`. This adjusts the target date calculation accordingly.
Why does the macro loop backwards from the last row?
When entire rows are deleted, the rows beneath them shift upward, changing their row numbers. Looping backwards (from bottom to top) ensures that shifting rows do not affect the row indices that still need to be checked.
Will this macro delete my column headers?
No, the code uses `For i = lastRow To 2 Step -1`, which means the loop intentionally stops at row 2, protecting the headers typically located in row 1.




