VBA & Macro Problems

How to Automatically Delete Excel Rows After a Specific Date Using VBA

Anum Mushtaq Oct 22, 2026 869 views

Question details

The user needs a VBA macro code to automatically delete entire rows in an Excel spreadsheet when a date in a specific column has passed.

How to Automatically Delete Excel Rows After a Specific Date Using VBA

Product: Excel
Device & OS: not provided
Scenario: Managing a spreadsheet where old records or expired dates need to be cleaned up automatically to maintain data relevance.
Observed behavior: The user currently has to manually delete rows with past dates and wants an automated solution where any row with a date earlier than today is removed automatically.

Before you start

Actions performed by a VBA macro usually clear the Undo stack, meaning you cannot undo the row deletion. Please create a complete copy or backup of your workbook before running the code.

Solution 1Recommended

Run a VBA Macro to Delete Rows with Past Dates

Use a custom VBA script to loop backward through your date column and delete any row where the date is older than today.

When deleting rows in VBA, it is crucial to loop backward (from the bottom up). If you loop forward, deleting a row shifts the remaining rows up, which changes their indices and causes the macro to skip evaluating the next row.

Open the VBA Editor

Press 'ALT + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to open a blank code window.

Paste the Deletion Code

Copy and paste the appropriate VBA script into the module. Ensure the script defines the last row and loops backward (e.g., For i = lastRow To 1 Step -1) while checking the value of the date column (e.g., Column D) against the current Date.

Run the Macro

Click anywhere inside the code and press 'F5' or click the 'Run Sub/UserForm' button (the green play arrow) in the toolbar to execute the script and delete the rows.

Run a VBA Macro to Delete Rows with Past Dates

Example VBA Code: Sub DeletePastDates() Dim i As Long Dim lastRow As Long lastRow = Cells(Rows.Count, "D").End(xlUp).Row For i = lastRow To 1 Step -1 If IsDate(Cells(i, "D").Value) And Cells(i, "D").Value < Date Then Rows(i).Delete End If Next i End Sub

Solution 2

Automatically Trigger the Macro When Opening the Workbook

Set the macro to run automatically every time the Excel file is opened so you do not have to trigger it manually.

Advanced Data Management

Easily Manage Macros and Automation in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to automate repetitive tasks like deleting expired rows effortlessly. It provides an intuitive Macro Editor to write, edit, and run your VBA code seamlessly.

1. Download and Open: Download and install WPS Office, then open your macro-enabled spreadsheet.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon.
3. Open Visual Basic Editor: Click 'Visual Basic' to access the code editor and paste your row-deletion script.
4. Run the Macro: Click 'Macros' on the ribbon, select your script, and click 'Run' to instantly clean up rows with past dates.

Fully compatible with Microsoft Excel (.xlsm) macro-enabled formats.Built-in Visual Basic Editor for writing and debugging VBA scripts.Lightweight and runs smoothly on all major operating systems.Free and comprehensive alternative for advanced data analysis and automation.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro deleting all the rows with past dates?

If your macro skips rows, it is likely because the loop is running from top to bottom. When a row is deleted, the rows below it shift up, causing the loop to skip the next row. To fix this, change your code to loop backward (e.g., 'For i = lastRow To 1 Step -1').

Can I recover rows that were deleted by a VBA macro?

No, actions performed by a VBA macro typically clear the Undo stack, meaning you cannot press Ctrl+Z to bring the rows back. Always create a backup of your file before running destructive macros.

How do I change the macro to check a different column instead of Column D?

In the VBA code, locate the column reference, typically written as 'Cells(i, "D")' or 'Range("D" & i)'. Change the "D" to your target column letter, such as "A" or "E", to check dates in that specific column.

Excel & Data Problems