VBA Delete an Entire Row Based on Cell Value in Excel

To delete entire Excel rows when a cell matches a value, loop through the worksheet from the bottom up and delete each matching row. In Excel for Microsoft 365 on Windows, the VBA macro below checks column C for the exact text “Delete”; change the column and target value to suit your data, and save a copy of the workbook before running it.

VBA macro for deleting matching rows

This example assumes row 1 contains headings, records begin in row 2, and the value to check is in column C on a worksheet named Data. It compares text without regard to capitalization, so “Delete,” “DELETE,” and “delete” all match. It does not treat a cell containing “Do not Delete” as a match.

Sub DeleteRowsWhenCellMatches()
    Const FIRST_DATA_ROW As Long = 2
    Const CHECK_COLUMN As String = "C"
    Const TARGET_VALUE As String = "Delete"

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

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

    ' Work upward so deleting a row does not skip the next record.
    For r = lastRow To FIRST_DATA_ROW Step -1
        If Not IsError(ws.Cells(r, CHECK_COLUMN).Value) Then
            If StrComp(CStr(ws.Cells(r, CHECK_COLUMN).Value), _
                       TARGET_VALUE, vbTextCompare) = 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

For example, if C2 contains “Keep,” C3 contains “Delete,” and C4 contains “DELETE,” the macro removes rows 3 and 4. Row 2 remains. It deletes the complete worksheet rows, including cells outside the columns in your data table, so check the sheet layout before using it.

How the macro works

The macro identifies the last used cell in the check column, then examines each row between that point and the first data row. StrComp compares the cell text with the target using vbTextCompare, which makes the text comparison case-insensitive. The IsError check prevents a cell containing an Excel error value such as #N/A from causing a conversion error.

FIRST_DATA_ROW tells VBA where records start. Set it to 3, for example, if the first two rows contain a title and headings.
CHECK_COLUMN is the column containing the value to test. Use a column letter such as “A” or “F”.
TARGET_VALUE is the exact value to match. For a different status, replace “Delete” with that status.
ws.Rows(r).Delete deletes the whole worksheet row, not just the cell in column C.

Using ThisWorkbook means the macro acts on the workbook that contains the code. The worksheet name must match the tab exactly. If the sheet is called “Orders,” change Worksheets("Data") to Worksheets("Orders"). This explicit reference is safer than relying on whichever sheet happens to be active when the macro runs.

Why the loop runs from bottom to top

Deleting a row shifts the rows below it upward. A loop moving downward can therefore skip a record: after row 3 is deleted, what was row 4 becomes row 3, but the loop may continue to row 4. A descending loop avoids that problem because deleting a row does not change the row numbers of records the loop has yet to inspect above it.

This is a useful general rule for VBA code that deletes rows one at a time: start at the last relevant row and move upward. If you use a different approach, such as filtering and deleting a block of visible rows, the mechanics differ, but you still need to ensure that the heading row and nonmatching records are protected.

Set up and run the macro

Save a backup copy of the workbook. Deleting rows with VBA cannot be reversed with Ctrl+Z.
Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if it contains the macro.
Open the Visual Basic Editor. On Windows, press Alt+F11. On Mac, use Developer > Visual Basic; the keyboard shortcut can vary with the keyboard and function-key settings.
In the editor, choose Insert > Module, then paste the code into the new module. Update the sheet name, column, target value, and first data row as needed.
Return to Excel and run the macro. On Windows, press Alt+F8, select DeleteRowsWhenCellMatches, and choose Run. You can also use Developer > Macros. On Mac, use Developer > Macros.

Excel may display a security notification when a workbook contains macros. Only enable macros in a workbook you trust. Excel for the web can open an .xlsm workbook, but it cannot run VBA macros; open the file in the desktop Excel app to execute this code. Excel for Mac supports VBA, although some features and external integrations differ from Windows.

Choose the right match rule

The sample uses an exact text match that ignores case. Choose a rule that fits the data rather than deleting rows based on a vague visual resemblance. A cell containing “Delete later” does not match “Delete” in the sample, and a numeric 1 is not automatically the same as the text “1” in every comparison context.

Exact text, ignore capitalization: Keep StrComp with vbTextCompare, as shown.
Exact text, capitalization matters: Change vbTextCompare to vbBinaryCompare. Then “Delete” and “delete” are different.
Contains a word or phrase: Use an explicit substring test, such as InStr(1, CStr(cellValue), TARGET_VALUE, vbTextCompare) > 0, in place of the exact comparison. Be cautious: this would also match longer text containing the target.
Number or date criteria: Compare values using the appropriate numeric or date logic rather than relying on a text match. Confirm how the worksheet stores the values and whether displayed formatting differs from the underlying value.

Excel cells can display a formatted value that differs from their stored value. For instance, a date may appear as “Nov 2” while VBA reads a date serial value. If the criterion is a date, a dedicated date comparison is generally more reliable than converting the displayed text and hoping it has the expected format.

Last-row choices and blank cells

The sample finds the last used row in the check column. That is a sensible choice when every row that could match has a value in that column. If the check column can be blank on the final records, the calculated last row may stop too early and those later rows will not be inspected. In that case, find the last row using a different column that is reliably filled for every record, or determine the data range in another way.

There is a related edge case when the criterion itself is a blank cell. A last-row calculation based on the check column cannot reliably establish how far records extend if that column is blank. Use a populated ID or another consistently filled field to establish the record boundary, and write the comparison to identify blanks deliberately. Do not simply delete every blank in a column unless you have confirmed that blank means “remove” in your data.

The End(xlUp) technique works well for a continuous dataset but can be affected by gaps and formulas that return an empty string. If the sheet has multiple separated blocks, or data below the main table, define the intended range explicitly. For Excel tables, consider using the table’s data body range so the macro is limited to actual table records.

Common problems and safer checks

“Subscript out of range”: Check that the worksheet tab is named exactly as written in the code, including spaces.
No rows are deleted: Verify the target value, check column, starting row, and whether cells contain extra spaces. A value such as “Delete ” has a trailing space and will not match “Delete.”
Rows above the data disappear: Set FIRST_DATA_ROW correctly. The loop should not include heading or title rows.
Some records are skipped: Confirm the loop counts downward and uses Step -1.
The wrong sheet changes: Keep the worksheet explicitly assigned, as in the sample, instead of using unqualified references to ActiveSheet.
Protected sheet or merged cells: Worksheet protection or unusual layouts may prevent deletion or produce an error. Review the sheet’s protection and structure before running the macro.

For a first run, test on a duplicate workbook or a small sample of records. You can also add a confirmation prompt before the loop, or first change the code to highlight qualifying records rather than delete them. A preview helps verify that the rule selects the intended records, especially when the workbook contains formulas, filters, or important data outside the apparent table.

VBA versus filtering and manual deletion

VBA is useful when the same deletion rule must be applied repeatedly, when the dataset is large, or when the task is part of a larger automation. For a one-time cleanup, Excel’s filter tools may be easier to inspect: filter the criterion column to show the target value, select the visible data rows without the header, and delete the selected sheet rows. Check carefully that the selection excludes the heading and includes only the intended records.

Manual review is often preferable when each deletion needs a human decision. Filtering does not automatically make deletion risk-free: hidden or filtered rows can make selections confusing, and deleting a whole worksheet row can affect neighboring data. If the records are in an Excel table, deleting table rows is often more appropriate than removing entire worksheet rows when unrelated content shares those rows. A macro should reflect that distinction if the worksheet contains multiple side-by-side datasets.

Frequently Asked Questions

Can VBA delete a row when a cell equals a number?
Yes. Use a numeric comparison when the cell holds a number, rather than comparing text blindly. Check for error values first, and confirm whether the cells contain true numbers or numbers stored as text.

How do I delete rows when a cell contains a word?
Replace the exact-match test with an InStr substring check. That finds the target within longer text too, so a search for “Delete” would also match “Do not Delete” unless you add logic to exclude it.

Can I delete rows based on a value in another column?
Yes. Change CHECK_COLUMN to the column you want to inspect. The code will still delete the entire worksheet row when that cell matches the target.

Why can’t I undo a VBA row deletion?
Running a VBA macro clears Excel’s normal undo history. Ctrl+Z will not restore rows deleted by the macro, which is why you should save a separate copy before running it.

Will this macro run in Excel for the web?
No. Excel for the web does not execute VBA. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the macro.

Does this code delete rows from an Excel table only?
No. ws.Rows(r).Delete deletes the entire worksheet row. If you need to remove only a table record while preserving other content in that worksheet row, adapt the code to target the table’s row instead.

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.