VBA & Macro Problems

How to Delete Excel Rows Matching Multiple Criteria with VBA

Aamir Naveed Akram Oct 9, 2026 869 views

Question details

The user wants to automate deleting specific rows in an Excel worksheet where cells in a target column match one of several text criteria (e.g., "Closed" or "New").

How to Delete Excel Rows Matching Multiple Criteria with VBA

Product: Excel
Device & OS: not provided
Scenario: Automating data cleanup by removing rows containing specific multiple text values using a VBA macro.
Observed behavior: The user needs a reliable VBA script that correctly loops through rows without skipping consecutive matching records during the deletion process.

Before you start

Because VBA macro actions generally cannot be undone, please ensure you save a backup copy of your Excel workbook before running any script that deletes rows.

Solution 1Recommended

Use a Reverse Loop and Select Case in VBA

The most reliable method to delete rows is by looping from the bottom up. This prevents the macro from skipping the next row when the current row is deleted and the remaining rows shift upward.

When you delete a row using a standard top-down loop, Excel shifts the rows below it up by one. This causes the loop to skip the next immediate row. Using a reverse loop (Step -1) completely avoids this indexing issue.

Additionally, using 'Select Case' instead of multiple 'If...Or' statements makes your code cleaner and easier to update when adding more criteria later.

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

Click 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.

Paste the Reverse Loop Code

Type or paste your macro. Ensure you define your row count starting from the bottom: 'count = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row', and set the loop to run backward: 'For i = count To 1 Step -1'.

Define the Deletion Criteria

Inside the loop, use a 'Select Case' statement on the target cell (e.g., 'ws.Cells(i, "A").Value'). Under 'Case "Closed", "New"', add the command 'ws.Rows(i).Delete' to remove the matching rows.

Run the Macro

Close the VBA editor and press 'Alt + F8'. Select your new macro from the list and click 'Run' to execute the bulk deletion.

Use a Reverse Loop and Select Case in VBA

Speed Up Your Macro: To make the macro run much faster, especially on large datasets, add 'Application.ScreenUpdating = False' at the beginning of your script and set it back to 'True' at the end.

Run VBA Macros Easily in WPS Spreadsheet

WPS Office offers robust, built-in support for VBA macros, allowing you to run your row-deleting scripts just as you would in Microsoft Excel. Enjoy a seamless, highly compatible spreadsheet experience without the heavy subscription costs.

1. Open your Workbook in WPS Office: Launch WPS Spreadsheet and open the Excel file containing your dataset.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, you can enable it in the WPS settings.
3. Run Your Macro: Click on 'Macros' or 'Visual Basic' to insert your reverse-loop code, then execute it to quickly clean up your rows.

Fully compatible with Microsoft Excel VBA macros and .xlsm filesLightweight installation and lightning-fast performance for data processingFamiliar interface with a dedicated Developer tab for easy macro managementFree to download and use for your daily office tasks

Frequently Asked Questions

Why does my VBA macro skip rows when deleting data?

When you use a standard forward loop (e.g., For i = 1 To 100), deleting row 10 causes row 11 to shift up and become the new row 10. The loop then moves to index 11, completely skipping the original row 11. Using a reverse loop (Step -1) processes rows from the bottom up, ensuring no rows are skipped as they shift downward.

How do I add more matching criteria to my VBA deletion rule?

If you are using a Select Case statement, you can easily add more criteria by separating them with commas. For example, change it to 'Case "Closed", "New", "Pending", "Canceled"' to delete rows matching any of those four words.

Can I delete rows based on conditions in multiple different columns?

Yes. Instead of Select Case, use an If...Then statement with logical operators like 'And' or 'Or'. For example: 'If ws.Cells(i, "A").Value = "Closed" And ws.Cells(i, "B").Value = "Expired" Then ws.Rows(i).Delete'.

Is there a faster alternative to looping through thousands of rows?

Yes. For exceptionally large datasets, applying an AutoFilter via VBA to hide non-matching rows and then using 'SpecialCells(xlCellTypeVisible).EntireRow.Delete' is often much faster than looping through each row individually.

Excel & Data Problems