VBA & Macro Problems

How to Use a VBA Macro to Delete Rows Older Than 5 Years

Nimra MalikNimra Malik Nov 10, 2026 868 views

Question details

The user wants to automate the deletion of table records that contain a date older than five years using a VBA macro.

How to Use a VBA Macro to Delete Rows Older Than 5 Years
Product
Spreadsheets
Device & OS
not provided
Scenario
Managing large datasets by cleaning up historical data that is no longer needed for active reporting.
Observed behavior
The user needs a reliable VBA script that correctly identifies dates older than 5 years and deletes the corresponding rows without skipping any records.
Before you start

Before running any macro that deletes data, export a backup copy of your workbook or archive the old records to a separate sheet for historical audit purposes.

Solution 1Recommended

Use a Reverse Loop VBA Macro to Delete Old Rows

Using a reverse loop ensures that deleting a row doesn't shift the remaining rows up, which would otherwise cause the macro to skip evaluating subsequent records.

When deleting rows in a spreadsheet using VBA, it is crucial to loop backwards (from the last row to the first). If you loop forwards, deleting row 5 makes the old row 6 become the new row 5, which the loop will then skip.

The DateAdd function dynamically calculates the exact date five years ago from the current system date, ensuring the macro works seamlessly over time.

1
Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.

2
Insert a New Module

In the VBA editor, click on 'Insert' in the top menu and select 'Module' to create a blank script window.

3
Paste and Customize the Code

Copy the macro script provided in the note below and paste it into the module. Ensure you change 'Table1' to your actual table name, and update the number '3' in 'tbl.DataBodyRange(r, 3)' to match your date column's index.

4
Run the Macro

Close the VBA editor. Back in your spreadsheet, press Alt + F8 to open the Macro dialog, select 'DeleteOldRows', and click 'Run'.

Use a Reverse Loop VBA Macro to Delete Old Rows
VBA Code Snippet for Deleting Old Rows: 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
Advanced Data Management with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks like deleting old records just as you would in Microsoft Excel.

  1. 1. Install WPS Office: Download and install the free WPS Office suite from the official website.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsm or .xlsx file containing the table.
  3. 3. Access Macro Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Macros', or press Alt + F8.
  4. 4. Execute the Cleanup: Select your custom data cleanup script and click 'Run' to process the rows instantly.
Fully supports VBA and Excel macro scripts100% compatible with Microsoft Excel (.xlsm) formatsLightweight and fast execution for large datasetsFree to use with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro skip rows when deleting data?

If your macro loops forward (e.g., For i = 1 To 100), deleting a row shifts the rows below it up by one. The loop then moves to the next index, inadvertently skipping the row that just shifted into the current index. Always use a reverse loop (Step -1) when deleting rows.

How do I change the macro to delete rows older than 3 years instead of 5?

In the VBA code, locate the line `FiveYearsAgo = DateAdd("yyyy", -5, Date)` and change the `-5` to `-3`. You can also change "yyyy" to "m" or "d" to calculate based on months or days.

Will this macro work if my data is not formatted as an official Table?

The provided script uses the `ListObject` property, which specifically targets official Tables (inserted via Insert > Table). If your data is a standard range, you must modify the code to loop through standard sheet rows using `ActiveSheet.UsedRange.Rows.Count` instead.

How can I archive the rows to another sheet instead of permanently deleting them?

Before the `.Delete` command inside the loop, add a line to copy the row to your destination sheet using a command like `tbl.ListRows(r).Range.Copy Destination:=Sheets("Archive").Cells(nextEmptyRow, 1)`.