VBA & Macro Problems

How to Delete or Clear Excel Entries Older Than 3 Months with a Macro

Ayan MasoodAyan Masood Nov 10, 2026 868 views

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.

How to Delete or Clear Excel Entries Older Than 3 Months Using a Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank script window.

3
Paste the VBA Code

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

4
Close the Editor

Save your work and close the VBA editor to return to your Excel worksheet.

Create and Run a VBA Macro to Remove Old Entries
ClearContents vs Delete: The code provided uses `ClearContents`, which leaves the row blank but preserves its formatting. Change `ws.Rows(i).ClearContents` to `ws.Rows(i).Delete` if you want the rows completely removed and the data shifted up.

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. 1. Open your file: Launch WPS Spreadsheets and open your .xlsm or .xlsx file containing the expense data.
  2. 2. Enable Macros: Navigate to the 'Developer' tab and ensure macros are enabled for the session.
  3. 3. Open VBA Editor: Click 'Visual Basic' or press ALT + F11 to insert or edit your macro scripts.
  4. 4. Run Data Cleanup: Run your macro or click the assigned form button to easily manage and clear old entries.
Full compatibility with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in VBA editor to automate repetitive tasks like data cleanup.Free, lightweight, and fast-loading spreadsheet application.User-friendly interface that feels instantly familiar.
microsoft office alternative - wps office

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.