VBA Delete Row Based on Cell Value in Excel

To delete Excel rows when a cell contains a chosen value, loop through the worksheet from the bottom row upward and delete each matching row. The VBA example below checks column C for the word Closed, ignores letter case and extra spaces, and keeps the header row intact.

How deleting rows based on a value works

VBA can inspect a cell on each row, compare its contents with a value you specify, and delete the entire worksheet row when the comparison is true. For example, a list might have order statuses in column C, and you may want to remove every record whose status is Closed. The condition determines which rows qualify; the delete instruction determines what gets removed.

The loop should normally run from the last data row toward the top. Deleting a row shifts the rows below it upward and changes their row numbers. If a loop moves downward, it can skip the row that shifts into the position just deleted. Moving upward avoids that problem: deleting row 12 does not change the position of rows 1 through 11, which the loop has yet to inspect.

The example assumes row 1 contains column headings, the values to test are in column C, and the worksheet is named Data. It deletes complete rows, not just the contents of the cells in column C. Check that this is what you want before running it.

A basic macro to delete matching rows

Put this procedure in a standard VBA module. Change the worksheet name, column letter, and comparison value to match your workbook. The macro checks the cells’ displayed results through Value2, so a formula that returns Closed also qualifies.

Sub DeleteRowsWhenStatusIsClosed()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim cellValue As Variant

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

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

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

        ' Ignore error-valued cells; compare text without case differences.
        If Not IsError(cellValue) Then
            If StrComp(Trim$(CStr(cellValue)), "Closed", vbTextCompare) = 0 Then
                ws.Rows(rowNum).Delete
            End If
        End If
    Next rowNum
End Sub

Trim$ removes leading and trailing spaces for the comparison, and vbTextCompare makes Closed, CLOSED, and closed equivalent. The cell itself is not edited before deletion. If a cell contains an Excel error such as #N/A, the macro skips it instead of trying to convert that error to text.

Set up and run the macro

Save a copy of the workbook before running a deletion macro. Excel cannot reverse VBA changes with Ctrl+Z.
Open the Visual Basic Editor. In Excel for Microsoft 365 on Windows, press Alt+F11, or select Developer > Visual Basic. On Mac, Option+F11 may open the editor; some keyboards require Fn as well. The Developer tab is also available in desktop Excel.
In the editor, choose Insert > Module, then paste the procedure into the new standard module. Replace Data and column C if your worksheet or test column has a different name.
Return to Excel and run the macro from the Macros dialog, or place the cursor inside the procedure in the editor and run it. Review the worksheet afterward to confirm the remaining records are correct.

To keep VBA code in the workbook, save it as an Excel Macro-Enabled Workbook (*.xlsm) and allow macros only when you trust the file and its source. Excel for the web can open macro-enabled workbooks, but it cannot run or edit VBA macros; use the desktop app. On Mac, VBA is supported in desktop Excel, though editor shortcuts and some interface details can differ.

Change the match rule

The comparison in the example uses an exact text match after trimming spaces. That is a useful default for a status, category, or identifier, but other tasks may need a different test. Adjust the condition inside the loop while keeping the bottom-up traversal and error check.

Check another column: Replace C in both the last-row calculation and the cell reference with the letter of the column that contains the value to test. Keeping those references aligned ensures the loop checks the intended column and finds its last populated row.
Use another exact value: Replace Closed with the text you need. If the comparison value is numeric, compare it as a number rather than converting it to text; confirm that the cells really contain numbers and not number-like text.
Require matching letter case: Change vbTextCompare to vbBinaryCompare in StrComp. For example, Closed and closed will then be treated as different strings.
Find text anywhere in a cell: Use a substring test rather than exact equality. InStr with a text-comparison option can detect a word within a longer description, such as finding late in Payment late. Decide whether phrases that merely contain the text should truly be deleted.
Use a numeric or date threshold: Compare the cell’s numeric value to a boundary, and check IsNumeric or IsDate as appropriate before making the comparison. Excel stores dates as serial numbers, so the intended date column and its values should be verified before deletion.

For multiple requirements, combine conditions deliberately. An AND rule deletes a row only when every test is true; an OR rule deletes it when at least one test is true. Write down the intended rule first—for instance, status is Closed and the archive flag is Yes—because changing AND to OR can greatly increase the number of deleted records.

Choose the right last-row method

The code finds the last row by looking upward from the bottom of column C. This is efficient when every data record has a value in the column being checked. If that column can be blank on valid records, the loop may stop too early and leave matches farther down the worksheet untouched.

In that case, use a column that is populated for every record, such as a unique ID, to determine lastRow, while continuing to test column C for the deletion condition. Another option is to find the last used cell across the worksheet, but worksheets with leftover formatting or old content can make a broad last-cell calculation include rows that are not part of the current data. The best method depends on how the sheet is structured.

If the only content is a header in row 1, the loop does not delete the header: its range starts at row 2. If there is no header, change the starting row to 1. Do not make that change if row 1 contains headings that must remain.

Common problems and how to avoid them

Wrong worksheet or workbook: Worksheet names must match, including spaces. ThisWorkbook means the workbook containing the VBA code, not necessarily whichever workbook is active. If the code is stored in Personal Macro Workbook or another file, explicitly refer to the workbook that contains the data.
Rows are skipped: A loop running from the top downward can miss adjacent matching rows after a deletion shifts records. Keep the Step -1 loop, or use a different approach that does not change row positions while scanning.
Some records remain: Check the column used to calculate lastRow. A blank near the bottom of that column can hide data farther down. Also confirm that the comparison is exact when the data contains extra spaces, different punctuation, or additional words.
Unexpected rows disappear: Check whether the macro is deleting a whole row, whether the match is case-insensitive, and whether the condition is exact or partial. Test with a copied workbook or change the macro temporarily to highlight qualifying rows before deleting them.
The macro will not run: Confirm the file is saved in a macro-enabled format, the code is in a standard module, and the workbook’s macro security settings permit this trusted macro to run. Excel for the web does not execute VBA.

It is also worth checking whether formulas or other worksheets depend on the rows being removed. Excel adjusts many direct references when rows are deleted, but deletion can still change the data available to formulas, charts, summaries, or downstream processes. A backup gives you a way to restore the original data if the result is not what you intended.

When filtering is a better alternative

For a one-time cleanup, using a filter can be easier to review than running code. Select the data range, choose Data > Filter, open the dropdown for the relevant column, and filter for the value. Check that only the intended records are shown before deleting the visible data rows; keep the header row out of the deletion. Then clear the filter and verify the remaining list.

Filtering makes the proposed matches visible before removal, which helps when the criterion is ambiguous or the workbook is unfamiliar. A macro is more useful when the same cleanup must be repeated, when many rows are involved, or when the rule has several conditions. Either method can permanently remove data, so make a copy first.

Frequently Asked Questions

Can VBA delete a row when a cell equals a number?
Yes. Use a numeric comparison against the cell’s value, after confirming the cell contains a number. Text that looks like a number may not behave the same way, so validate the data type and avoid comparing numeric values as text unless that is intentional.

Can I delete rows when a cell is blank?
Yes. Test the relevant cell for an empty value, and decide whether formulas returning an empty string should count as blank. Also choose a reliable column for finding the last data row, since a blank test column cannot by itself identify records below its last populated cell.

Does the macro delete only the matching cell?
No. The example uses ws.Rows(rowNum).Delete, which deletes the entire worksheet row, including its other columns. To clear just a cell or a range instead, use a clear operation on that specific range rather than deleting the row.

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 the deleted rows. Save a separate copy before running the code, especially when the selection rule has not been checked on a small sample.

Will it work if the cell contains a formula?
Yes, if the formula’s result matches the criterion. Value2 reads the result stored for the cell, not the formula text itself. If you need to test the formula text rather than its result, the comparison must use a different cell property.

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.