VBA Delete Rows With a Specific Cell Value in Excel

To delete Excel rows when a cell in a chosen column contains a specific value, loop upward from the last used row and delete each matching row. The VBA example below checks column C, keeps row 1 as a header, and removes rows whose value is “Closed”; save a copy of the workbook before running it because macro deletions cannot be undone with Ctrl+Z.

How the VBA deletion method works

A VBA loop can inspect a chosen cell on each worksheet row, compare it with your target, and delete the entire row when it matches. The important detail is to loop from the bottom upward. Deleting a row shifts the rows below it up, so a top-to-bottom loop can skip the row that moves into the position just checked. Starting at the last row and counting down avoids that problem.

The sample assumes the data is on a worksheet named Data, the values to check are in column C, and row 1 contains headings. It treats text matching as case-insensitive but otherwise checks the whole cell value: “Closed” matches, while “Closed – reviewed” does not.

Prepare the workbook before running a macro

Before editing or running VBA, make a separate copy of the workbook. A macro that deletes rows changes the workbook immediately, and Ctrl+Z cannot reverse a macro’s actions. Check that the worksheet name, column letter, and first data row in the code match your layout. If the workbook contains important content in other columns on the same rows, confirm that deleting those entire worksheet rows is what you intend.

Save the file in a macro-capable format such as Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in that file.
Use a trusted copy of the workbook and allow macros only when you understand what the code does.
Confirm the target value and inspect the matching rows before running the macro on your original data.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then use Insert > Module and paste the code into the module. On a Mac, open the editor from Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings. Excel for the web does not run VBA macros, so open the workbook in the desktop application to use this code.

VBA code to delete rows with a matching value

This version skips error values such as #N/A rather than trying to compare them as text. It uses Value2, so it compares the cell’s underlying value rather than a number’s displayed formatting.

Option Explicit

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

    ' Set the worksheet and exact text to find.
    Set ws = ThisWorkbook.Worksheets("Data")
    target = "Closed"

    ' Find the last used row in the column being checked.
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Count upward so deleting a row does not skip the next record.
    For r = lastRow To 2 Step -1
        cellValue = ws.Cells(r, "C").Value2

        If Not IsError(cellValue) Then
            ' Compare whole values without distinguishing letter case.
            If StrComp(CStr(cellValue), target, vbTextCompare) = 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

To run it in the Visual Basic Editor, place the cursor inside the procedure and choose Run > Run Sub/UserForm. You can also return to Excel and run it from Developer > Macros. If the Developer tab is missing on Windows, enable it through File > Options > Customize Ribbon. On Mac, use Excel > Preferences > Ribbon & Toolbar to show the Developer tab.

Change the column, target, and starting row

Adapt the three settings in the sample to your sheet. Change “Data” to the worksheet tab name, “C” to the column containing the criterion, and “Closed” to the value to remove. The loop begins at row 2 because the example has a header in row 1. If there is no header and data starts in row 1, change the loop’s ending value from 2 to 1.

The comparison converts the cell value to text before checking it. That is convenient for text labels and can also make a numeric value such as 123 match the target text “123”. However, if a number is stored as 123 and merely displayed as 00123 with a number format, Value2 still reads 123; the displayed leading zeros do not become part of the comparison. For identifiers where zeros matter, store the identifiers as text or compare the intended underlying values deliberately.

By default, StrComp with vbTextCompare ignores differences in capitalization, so “closed” and “CLOSED” also match. For a case-sensitive comparison, replace vbTextCompare with vbBinaryCompare. To ignore spaces at the beginning or end of a cell, compare Trim$(CStr(cellValue)) with target, but do that only if removing those spaces is appropriate for your data.

Exact value versus text that contains a value

“Specific value” can mean either an entire cell equals the criterion or a cell includes the criterion somewhere in a longer string. The sample uses an exact whole-cell comparison. If column C contains “Closed – reviewed,” it will remain, because that cell is not equal to “Closed.” This distinction prevents a short target from unexpectedly removing more records than intended.

If you mean “contains this text,” use InStr instead of StrComp inside the error check. For example, the condition can be If InStr(1, CStr(cellValue), target, vbTextCompare) > 0 Then. That finds the target anywhere in the cell and ignores case. Review the possible matches first: a target such as “old” could occur inside a longer word, not just as a separate status. For whole-word matching or more complex rules, define the rule carefully before deleting anything.

Handling blanks, formulas, and large lists

The sample’s last-row calculation uses column C. That is a sensible choice when column C is filled for every record, but the loop cannot reach records below its last nonempty cell in that column. If the criterion column may be blank in the final records, find the last row using a different consistently populated column, such as an ID column, while continuing to test column C.

A formula cell is compared by its calculated result through Value2. If the formula returns “Closed,” it can match; if it returns an error, the macro skips it. An actually empty cell converts to an empty string. With the sample target set to “Closed,” empty cells are not deleted. Do not change the target to an empty string unless you specifically want to remove rows where the checked cell is empty.

Deleting one row at a time is easy to understand and works well for modest lists, but can take a while on very large sheets. For many thousands of matches, filtering and deleting the visible matching records in a batch can be quicker. Test any faster version on a copy first, especially when the range contains hidden rows, totals, or other data.

Use Excel’s filter instead of VBA

If this is a one-time cleanup, a filter may be simpler and avoids enabling a macro. Select a cell in the data range and choose Data > Filter. Open the filter arrow for the criterion column, select only the value to remove, and inspect the displayed records. Select the matching data rows, excluding the header, then use Home > Delete > Delete Sheet Rows. Clear the filter afterward and confirm that the intended records are gone.

Filtering is also useful as a preview before running VBA: it lets you see which rows would match. Take care with filtered selections and hidden rows. Confirm that the selected rows are the matching visible records, not the entire dataset, before deleting. For repeated cleanups with changing target values, a macro can be more consistent; for a single small edit, the filter is often easier to review.

Common problems and safer alternatives

Subscript out of range: The worksheet name in the code does not exactly match the tab name. Check spelling and spaces, or change the name in the Set ws line.
No rows are deleted: Check the column letter, target text, and starting row. Look for extra spaces, different punctuation, or a longer cell value. The sample ignores letter case but does not ignore spaces.
Some records are missed: If the criterion column has blanks near the bottom, lastRow may stop too early. Base lastRow on a column that is populated throughout the records.
Other information disappears: ws.Rows(r).Delete removes the whole worksheet row, including cells outside the data table. If unrelated content sits beside the list, delete only the table’s data rows or move the list to its own sheet.
The macro will not run: Excel for the web does not execute VBA. In the desktop app, check that the file is macro-enabled and that its macros are permitted by your organization’s security settings.

For a recurring process where deletion is not essential, consider marking matching records in a helper column and filtering them for review. That preserves the source data and creates an audit-friendly check before removal. Another option is to copy nonmatching rows to a new sheet or workbook; this takes more setup but makes it easier to compare the cleaned result with the original.

Frequently Asked Questions

Can I delete rows when a cell equals a number?
Yes. The sample converts the cell value to text, so a numeric value such as 25 can match target text “25”. Remember that displayed number formatting, including leading zeros, is not necessarily part of the underlying value.

Does the macro delete rows if the value is only part of the cell?
No. StrComp checks whether the whole cell value matches the target. Use an InStr condition if you intentionally want to match text contained within a longer cell value.

Can I delete rows based on two columns?
Yes. Add another condition inside the loop, such as checking a status in column C and a category in column D. Decide whether both conditions must be true or either one may be true; use And for both and Or for either.

Can I undo a VBA row deletion with Ctrl+Z?
No. Excel clears its normal undo history when a macro changes the workbook. Save a copy before running the macro, and check the matching records before deletion.

Will this macro work in Excel for the web?
No. VBA macros run in the desktop Excel application, not Excel for the web. Open the workbook in desktop Excel or use a filter for a manual cleanup in the browser.

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.