VBA & Macro Problems

How to Delete Excel Rows When Values Do Not Match Column N Using VBA

Algirdas Jasaitis Oct 22, 2026 868 views

Question details

The user needs to fix a VBA macro designed to delete specific rows when a cell value does not match any data in Column N.

How to Delete Excel Rows When Values Do Not Match Column N Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing or debugging an Excel VBA script to clean up dataset rows based on unmatched values compared to a reference column (Column N).
Observed behavior: The current VBA code incorrectly uses WorksheetFunction.Match, ActiveCell, and Selection, which leads to runtime errors or the deletion of incorrect cells when a match is not found.

Before you start

Always create a backup copy of your Excel workbook before running macros that delete rows, as VBA deletion actions cannot be undone via the standard Undo feature.

Solution 1Recommended

Use Application.Match and a Bottom-Up Loop

The safest and most reliable method is to avoid selecting cells and iterate backwards through the rows using a Long variable.

Relying on ActiveCell and Selection slows down the macro and increases the risk of deleting the wrong data. Furthermore, WorksheetFunction.Match raises a terminating runtime error when a value is not found.

Instead, use Application.Match combined with IsError. This approach gracefully handles unmatched values. Always loop from the bottom row up to the top row to prevent skipping rows as data shifts up during deletion.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Insert a new Module from the 'Insert' menu.

Set Up a Bottom-Up Loop

Declare a Long variable for the row index. Write a For loop that counts backwards, such as: For i = 300 To 1 Step -1.

Apply Application.Match with IsError

Inside the loop, check for a match using: If IsError(Application.Match(Cells(i, 1).Value, Range("N1:N300"), 0)) Then

Execute the Row Deletion

Inside the If statement, add the deletion command: Rows(i).Delete. Close the statement with End If and Next i.

Use Application.Match and a Bottom-Up Loop

Why Loop Backwards?: Looping backwards ensures that when a row is deleted, the upward shift of the remaining rows does not affect the unchecked row indices, preventing the macro from skipping data.

Solution 2

Correcting the ActiveCell.Offset Method

If your macro is built around ActiveCell navigation, you must implement safe error handling to prevent the code from breaking on unmatched values.

Powerful Data Processing

Delete Unmatched Rows Easily in WPS Spreadsheet

If you want to avoid dealing with complex VBA macros, WPS Spreadsheet allows you to quickly identify and delete rows with unmatched values using powerful built-in functions and advanced filtering.

1. Create a Helper Column: Insert a new column next to your dataset to act as an indicator.
2. Use the COUNTIF Formula: Enter =COUNTIF($N$1:$N$300, A2) in the helper column and drag the fill handle down to apply it to all rows.
3. Filter the Results: Select the header of the helper column, go to the 'Data' tab, and click 'Filter'. Uncheck all values except '0'.
4. Delete the Rows: Select all the visible rows that filtered as '0' (unmatched), right-click, and select 'Delete Row'. Finally, clear the filter.

100% compatible with Microsoft Excel (.xlsx) formats and formulasIntuitive Advanced Filter and Conditional Formatting featuresFree, lightweight, and fast alternative for large datasetsNo programming knowledge required to clean up data

microsoft office alternative - wps office

Frequently Asked Questions

Why does WorksheetFunction.Match cause an error in VBA?

When WorksheetFunction.Match cannot find the specified value, it throws a runtime error that immediately halts the macro. Using Application.Match instead returns an error value that can be safely evaluated using IsError() without stopping the script.

Why should I loop backwards when deleting rows in Excel VBA?

If you loop forwards (e.g., from Row 1 to 10) and delete Row 2, the old Row 3 shifts up to become the new Row 2. The loop then advances to Row 3, skipping the data that just moved up. Looping backwards from the last row prevents this shifting issue.

Can I delete rows based on another column's values without VBA?

Yes. You can use formulas like COUNTIF or VLOOKUP in a helper column to identify non-matching rows. Once identified, you can use the Filter tool to display only the unmatched rows, highlight them, and delete them manually.

What is the difference between Selection and qualifying ranges in VBA?

Using Selection or ActiveCell relies on the user interface cursor, which is slow and prone to errors if the active worksheet changes. Qualifying ranges (e.g., Worksheets("Sheet1").Range("A1")) directly references the data in memory, making your macro faster and more reliable.

Excel & Data Problems