Excel VBA: Delete Rows Based on Criteria

To delete Excel rows that meet a condition, loop through the relevant rows from bottom to top and delete each match; working upward prevents skipped rows. In Excel for Microsoft 365 on Windows, put the VBA in a standard module, change the worksheet, column, and criterion to match your data, then run it only after saving a backup.

How row deletion works

Excel shifts rows upward when you delete one. If a macro checks row 2 and deletes it, the old row 3 moves into row 2. A loop that continues downward to row 3 has already passed that new occupant, so it may leave a matching record behind. Counting down from the last row to the first avoids this problem: deleting a row does not change the position of any unexamined row above it.

Decide what counts as a match before writing the macro. A criterion might be an exact status such as Cancelled, a blank cell, a date before a cutoff, or a value below a threshold. Also identify the header row and the column that contains the criterion. The examples below assume row 1 has headings and records begin in row 2.

Delete rows that match an exact value

This basic macro checks column C and deletes a complete worksheet row when the cell equals Cancelled. It finds the last used row in column C, then checks upward until row 2. Replace Data, C, and Cancelled with the names and values that apply to your workbook.

Sub DeleteCancelledRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Keep row 1 as the header; check records from bottom to top.
    For r = lastRow To 2 Step -1
        If ws.Cells(r, "C").Value = "Cancelled" Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

This comparison is an exact match: a cell containing Cancelled – review does not match. In VBA, string comparisons are normally case-sensitive unless the module sets a different comparison option. For a case-insensitive exact match, use StrComp with vbTextCompare. If the comparison column can contain Excel error values, check for errors first; converting an error cell directly to text can cause a runtime error.

The last-row calculation depends on column C having a value in the final record. If that column can be empty in the bottom records, find the last row using a different column that is reliably populated, such as a record ID. Otherwise, the macro may never inspect rows below the last non-empty cell it finds in C.

Choose exact, partial, blank, or multiple criteria

Change the If condition to express the rule you actually need. For a case-insensitive exact text match in column C, the condition can be:

If StrComp(CStr(ws.Cells(r, "C").Value), "Cancelled", vbTextCompare) = 0 Then

For a partial match, use InStr. This example matches any cell in column C that contains the word overdue, without distinguishing uppercase from lowercase:

If InStr(1, CStr(ws.Cells(r, "C").Value), "overdue", vbTextCompare) > 0 Then

For records with either of two exact statuses, combine conditions with Or. Use And when every stated condition must be true. For instance, a rule might require both a status in column C and a region in column D. Be deliberate about parentheses when combining And and Or, since VBA evaluates these operators in an order that may not match the way a sentence sounds.

To delete rows where a cell is empty or contains only spaces, use a trimmed string check:

If Len(Trim$(CStr(ws.Cells(r, "C").Value))) = 0 Then

This treats a formula returning an empty string as blank too. It does not treat a cell containing an error value as blank, so handle errors separately if they are possible. For a numeric threshold, compare the cell value to a number, such as less than 0; confirm that the column contains numbers rather than text that only looks numeric.

Use AutoFilter for large lists

A bottom-up loop is easy to understand and works well for ordinary lists. For a large contiguous range, filtering the matching records and deleting the visible data rows in one operation can be more efficient. The example below assumes headers in A1:F1, records beneath them, and the criterion in the third column of that range, which is column C.

Sub DeleteCancelledWithFilter()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim visibleRows As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    Set dataRange = ws.Range("A1:F" & lastRow)
    dataRange.AutoFilter Field:=3, Criteria1:="Cancelled"

    ' Select visible records only, excluding the header.
    On Error Resume Next
    Set visibleRows = dataRange.Offset(1, 0).Resize(dataRange.Rows.Count - 1) _
        .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not visibleRows Is Nothing Then visibleRows.EntireRow.Delete
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
End Sub

Field:=3 means the third column within dataRange, not necessarily worksheet column C; if the range starts in another column, count from its left edge. The code uses column A to find the last row, so A should be populated for every record. AutoFilter can interact with filters already in use on the sheet; run this on a range with no conflicting filter state, or adapt the macro to preserve the workbook’s existing filter setup. The error-handling lines account for a filter that returns no visible data rows, so the header is not deleted.

For criteria involving dates, numbers, or multiple conditions, check AutoFilter’s criteria syntax against the data type and Excel version in use. A loop can be clearer when the rule is complex or depends on calculations across several columns. Do not assume that filtering a range and filtering an Excel Table are interchangeable in every workbook: tables have their own headers and filter controls, so test the selected range before allowing a macro to delete records.

Run the macro safely

Save a separate copy of the workbook before running any deletion macro. VBA row deletions cannot be reversed with Ctrl+Z.
In desktop Excel for Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the code into the module, and change the sample worksheet name and criteria.
Return to Excel and run the macro from the Developer tab’s Macros command, or select the procedure in the editor and press F5. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). Use only macros you trust, and follow your organization’s macro-security policy.

Excel for the web cannot create, edit, or run VBA macros. Use desktop Excel to execute the code. Excel for Mac supports VBA, but the Visual Basic Editor and keyboard shortcuts can differ; use the Developer tab or Excel’s Tools > Macro menu where available rather than assuming Windows shortcuts work unchanged. The macro itself may also need adjustment if it relies on Windows-only components, though the row-deletion examples here use standard Excel VBA objects.

Common problems and how to avoid them

Rows are skipped: The loop probably runs from top to bottom. Start at lastRow and use Step -1.
No rows are deleted: Check the worksheet name, criterion column, header row, and whether the comparison is exact. Trailing spaces or different capitalization can prevent a match.
Some records are missed at the bottom: The chosen column for lastRow may have blanks in later records. Base the calculation on a reliably populated column or calculate the last used row across the relevant range.
The header disappears: Make sure the loop starts at row 2, or ensure the filtered deletion range excludes its header.
The wrong sheet changes: Qualify references with a specific worksheet object, as in the examples. Unqualified Rows or Cells references can act on whichever sheet is active.
The macro stops with an error: Check for worksheet protection, merged cells, error values, or an incorrect range. A protected sheet may need to be unprotected before row deletion is allowed.

Deleting an entire worksheet row affects every column on that row, not just the cell used to test the criterion. That is normally right for a table of records. If you only want to clear values in a particular range while keeping the rows in place, clear the cells instead of deleting the rows; these are different operations.

When to use a macro or another method

VBA is useful when the same deletion rule must be repeated, when the rule has several conditions, or when a button or scheduled workflow is part of a desktop process. A manual filter is often simpler for a one-time cleanup: filter the criterion column, select the visible records without the header, delete the sheet rows, then clear the filter. Review the selected records first, because a mistake in the filter can remove valid data.

Power Query is another option when the goal is to import, transform, and refresh a cleaned version of data rather than destructively edit the source. In Power Query, filter out unwanted records and load the resulting table. The source remains available for later refreshes, which can be preferable when the original data must be retained. If the task is merely to hide records temporarily, filtering is safer than deleting them.

Frequently Asked Questions

Can I delete rows based on more than one column?
Yes. In a loop, test both cells in the If condition and combine the tests with And or Or. For example, require the status in column C to match and the region in column D to match. Use parentheses to make combined conditions clear.

How do I delete rows when a cell is blank?
Test the criterion cell with Len and Trim, as shown above, so cells containing only spaces also count as blank. If the cells may contain formula results or errors, decide explicitly whether those should qualify and handle errors before converting values to text.

Does deleting rows with VBA work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; alternatively, use a manual filter or a supported data-cleaning workflow.

Can I undo a VBA row deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z will not restore rows deleted by the macro. Save a copy first or use a non-destructive approach such as loading filtered results separately.

Will the macro delete the whole row or just the matching cell?
The examples call Rows(r).Delete, which removes the entire worksheet row and shifts rows below it upward. To retain the row structure, clear the relevant cell contents instead; do not use whole-row deletion when neighboring columns contain unrelated information that must remain aligned.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.