Excel VBA: Delete Rows Based on a Cell Value Match

To delete worksheet rows when a cell matches a value, loop upward from the last used row and delete each match. In Excel for Microsoft 365 on Windows, put the macro below in a standard VBA module, set the worksheet, column, and target value, then run it; it checks column A from row 2 down and matches “Delete” without regard to letter case.

Sub DeleteRowsByValue()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim target As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    target = "Delete"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Work upward so deleting a row does not skip the next candidate.
    For r = lastRow To 2 Step -1
        If Not IsError(ws.Cells(r, "A").Value2) Then
            If StrComp(CStr(ws.Cells(r, "A").Value2), target, vbTextCompare) = 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

Change “Sheet1,” “A,” “Delete,” and the starting row to fit your workbook. The example assumes row 1 contains headers. Save a separate copy before running it: deleting rows with VBA cannot be reversed with Ctrl+Z.

What the macro deletes, and what it leaves alone

The test looks at one cell in each row, in column A. If that cell’s value equals the target, the macro deletes the entire worksheet row, including any values or formulas in other columns on that row. It does not just clear the matching cell. Rows whose column A value differs are left in place.

The comparison in the example is case-insensitive: “Delete,” “DELETE,” and “delete” all match. It is an exact match, however. A cell containing “Delete later” does not match “Delete.” The code also skips cells that contain Excel error values such as #N/A rather than stopping with a type mismatch.

For example, if A2:A5 contains Keep, Delete, DELETE, and Review, running the macro removes rows 3 and 4. The remaining rows shift upward, as they do when you delete rows manually. If column A contains formulas, the comparison uses the cells’ calculated values through Value2, not the formula text.

Set up and run the macro

VBA runs in the desktop version of Excel, not Excel for the web. In Windows Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the complete code into the module, and edit the worksheet name and target settings. Return to Excel and run the macro from Developer > Macros, or select it in the editor and press F5.

If the Developer tab is not visible, enable it in File > Options > Customize Ribbon by checking Developer. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as .xlsx removes the VBA project. Only enable macros in workbooks you trust.

On a Mac, Excel desktop also supports VBA. Open the Visual Basic Editor from the Developer tab or the Tools > Macro menu, depending on the Excel layout, and insert a standard module as above. Keyboard shortcuts can depend on the Mac keyboard and system settings, so using the ribbon or menu is a dependable alternative. Excel for the web can store a macro-enabled workbook but does not run VBA macros; open it in desktop Excel to execute the code.

Why the loop runs from the bottom upward

Deleting a row changes the row numbers beneath it. Suppose rows 5 and 6 both match. If a macro starts at row 1 and deletes row 5, the old row 6 moves into position 5. A loop that then advances to row 6 misses that moved record. This is the common reason a top-to-bottom deletion loop leaves some matching rows behind.

Starting at the bottom avoids that shift problem. When row 6 is deleted, rows above it keep their existing row numbers, so the loop can safely move to row 5, then row 4, and continue upward. The expression “Step -1” makes the loop count downward. It is useful for any VBA routine that removes rows from a range while iterating through it.

The code finds its final row by looking upward from the bottom of column A. This is efficient for a long list and makes the scan adapt when records are added. Because it uses column A to locate the end, that column should contain a value on every record row that needs checking. If another column is more consistently populated, use that column in the lastRow expression while keeping the match test pointed at the intended field.

Change the match rule

Choose the comparison to reflect what “based on a cell value” means in your data. Exact matching is generally safer than searching for a substring: it avoids deleting a row with a longer value that happens to contain the target text. For text that may contain extra spaces, decide whether spaces are meaningful before trimming them automatically.

Use another column: replace both occurrences of column A in the code with the column to inspect, such as C. Keep the lastRow column consistent with a reliably populated field.
Use a different target: change the target assignment to the text you want to find. For a numeric comparison, use a suitable numeric variable and compare numeric values rather than relying on their text representation.
Make text matching case-sensitive: change vbTextCompare to vbBinaryCompare in StrComp. Then uppercase and lowercase spellings are treated as different.
Match part of a cell: replace the inner StrComp condition with an InStr test. The following complete macro removes rows when column A contains the target anywhere in its text, without regard to case:

Sub DeleteRowsContainingText()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim target As String
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    target = "Delete"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' InStr returns a positive position when the text is present.
    For r = lastRow To 2 Step -1
        cellValue = ws.Cells(r, "A").Value2
        If Not IsError(cellValue) Then
            If InStr(1, CStr(cellValue), target, vbTextCompare) > 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

In that version, a value such as “Do not Delete” also contains the target and will be removed. If that is not intended, keep the exact-match test or define a more specific rule. To ignore leading and trailing spaces, you can compare Trim(CStr(cellValue)) with the target, but trimming changes the rule: a cell containing “ Delete ” will then count as a match.

Deleting rows in an Excel table

If the data is an Excel table, you can remove matching table records through the table’s ListRows collection. This targets rows in the table rather than deleting the same-numbered entire worksheet row, which matters when unrelated data sits beside the table. The example expects a table named Table1 and a table column named Status.

Sub DeleteMatchingTableRows()
    Dim tbl As ListObject
    Dim i As Long
    Dim target As String
    Dim cellValue As Variant

    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    target = "Delete"

    ' An empty table has no DataBodyRange to inspect.
    If tbl.DataBodyRange Is Nothing Then Exit Sub

    For i = tbl.ListRows.Count To 1 Step -1
        cellValue = tbl.ListColumns("Status").DataBodyRange.Cells(i, 1).Value2
        If Not IsError(cellValue) Then
            If StrComp(CStr(cellValue), target, vbTextCompare) = 0 Then
                tbl.ListRows(i).Delete
            End If
        End If
    Next i
End Sub

Use the table’s actual name and the exact header text for its match column. Table names are available on the Table Design tab when the table is selected. A table-specific deletion keeps the operation scoped to that table; a worksheet-row deletion affects cells across the whole sheet. Choose based on whether the records share their worksheet rows with other data.

Alternatives, speed, and common problems

For a small list, filtering the data manually and deleting the visible records may be easier than maintaining a macro. For a repeated cleanup, VBA is convenient because it applies the same rule consistently. AutoFilter can also be automated for larger datasets, but its code must account for headers, existing filters, hidden rows, and the range being filtered. The backward loop is straightforward when the worksheet is not excessively large.

No rows are deleted: check the sheet name, target column, target spelling, and starting row. A macro pointed at Sheet1 will not automatically use whichever worksheet is active.
Some matches remain: confirm that the values are exact matches, or use a deliberate partial-match rule. Look for extra spaces, different data types, or a last-row column with gaps.
The wrong rows are removed: stop and restore the saved copy. Check whether the macro deletes whole worksheet rows or just table records, and verify its target before another run.
The macro reports an error: confirm that the named sheet, table, and table column exist. A misspelled ListObject or column name causes an error in the table version.

For very large ranges, deleting one row at a time can take longer because Excel repeatedly updates the worksheet. A filter-and-delete strategy or building a new output range may be faster, but those approaches are more complex and should be tested on a copy. Do not disable calculation or events casually: other workbook features may depend on them, and settings need to be restored even if code stops unexpectedly.

Safety checks before deleting data

Row deletion is destructive, and macro actions are not recorded in a way that Ctrl+Z can undo. Save a copy under a different name before running the procedure, especially when the workbook contains formulas, linked data, or records that are difficult to recreate. Test against a small duplicate sheet first and inspect which rows would match before allowing the macro to delete them.

A useful test is to temporarily replace the Delete line with a harmless action, such as coloring the candidate cell or printing its row number in the Immediate window. Once the list of candidates is correct, restore the deletion line. Another option is to add a confirmation prompt before the loop, particularly for a macro used by other people. The key safeguard is to verify the sheet, field, match rule, and scope before changing data.

Frequently Asked Questions

Can VBA delete a row when a cell equals a number?
Yes. Compare the cell’s numeric value with a numeric target rather than converting both values to strings. This avoids ambiguity around formatting, such as a number displayed with leading zeros. Make sure the cells contain numbers, not text that only looks numeric.

How do I delete rows when a cell is blank?
Use a condition that checks the relevant cell for an empty value, and still loop from the last row upward. Decide whether a formula returning an empty string should count as blank; that is different from a truly empty cell. Also ensure the macro does not delete header rows by setting the correct first data row.

Can the macro delete rows containing a word instead of an exact match?
Yes. Use InStr with a text comparison, as in the partial-match example. That removes cells containing the word within longer text, so review the rule carefully to avoid unintended matches.

Will deleting rows with VBA work in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in Excel desktop for Windows or Mac to run the macro; alternatively, use a manual filter or another supported automation method.

Why can’t I undo a VBA row deletion?
Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot restore rows deleted by the macro. Keep a separate saved copy before running it and verify the results before continuing to edit the workbook.

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.