VBA Delete Row If Cell Contains Text

To delete rows when a cell contains particular text, use VBA’s InStr function in a loop that checks the target column from bottom to top. For example, the macro below deletes rows where column A contains “cancel”; change the sheet name, search text, and column to match your data.

A bottom-up macro for deleting matching rows

This example assumes the worksheet is named Sheet1, row 1 contains headers, and the cells to inspect are in column A. It searches for the substring “cancel” without regard to capitalization, so “Cancel,” “CANCEL,” and “cancelled” all match.

Option Explicit

Sub DeleteRowsIfColumnContainsText()
    Dim ws As Worksheet
    Dim searchText As String
    Dim lastRow As Long
    Dim r As Long
    Dim cellValue As Variant

    ' Set the worksheet and the text to find.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    searchText = "cancel"

    ' Prevent an empty search string from matching every cell.
    If Len(searchText) = 0 Then
        MsgBox "Enter text to search for before running the macro."
        Exit Sub
    End If

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

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

        ' Ignore cells containing Excel errors.
        If Not IsError(cellValue) Then
            If InStr(1, CStr(cellValue), searchText, vbTextCompare) > 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

Before running any deletion macro, save a separate copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the criteria or target range is wrong.

Why the loop runs from the bottom upward

Deleting a row shifts the rows below it up by one. If a macro scans downward, deleting the current row can move the next record into the row number the loop is about to leave behind. That can cause a matching row to be skipped. Starting at the last row and counting down avoids this problem: deleting a row does not change the row numbers of the records still waiting above it.

The loop starts at row 2 because the example treats row 1 as a header. If your data has no header, change the loop’s starting boundary so it includes row 1. Do not change the direction to count upward; keep the bottom-up pattern whenever the macro deletes rows as it scans.

How “contains” matching works

InStr searches for one string inside another. It returns a position when it finds the search text and returns zero when it does not. Therefore, the condition InStr(...) > 0 means that the cell contains the requested text somewhere, not necessarily that the entire cell equals it. For example, searching for “cancel” matches “cancelled order” and “please cancel,” but not “canceled” if the spelling differs.

The fourth argument, vbTextCompare, makes the comparison case-insensitive. To require matching capitalization, replace it with vbBinaryCompare. The search is literal: characters such as an asterisk are not treated as wildcard symbols by InStr. If you need wildcard criteria, use a different approach, such as the worksheet function COUNTIF with a carefully constructed criterion.

The macro converts the cell’s value to text with CStr. It can therefore also search the displayed underlying value of numbers or formula results in the checked column. For mixed data, consider whether that is desirable: searching for “12” could match a numeric value of 12, for example. Cells containing Excel errors are skipped because converting an error value to text can raise a VBA error.

Choose the right sheet, column, and last row

Update Worksheets("Sheet1") to the actual worksheet tab name, including spaces and punctuation. ThisWorkbook refers to the workbook that contains the macro. If the code is stored in a separate macro workbook and should act on the currently active workbook instead, the workbook reference needs to be changed deliberately; relying on the active sheet without checking it can delete data from the wrong file.

Change both occurrences of column A in the sample if you want to inspect another column. The last-row calculation also uses column A, which is suitable when that column is populated for every record. If records can have blanks in A while other columns contain data farther down, this calculation may stop too early. Use a consistently populated key column for both the search and boundary, or determine the last used row based on another reliable column.

Rows are deleted from the entire worksheet using ws.Rows(r).Delete, not just cleared in the target cell. That is usually what “delete row” means, and it keeps each record together across columns. If you only want to remove the cell contents while preserving the records and layout, clear the cell instead; that is a different operation and does not shift the remaining rows.

Exact matches, multiple columns, and other criteria

If the requirement is “delete when the cell equals this value” rather than “contains this text,” compare the cell value directly or use StrComp. Exact matching avoids deleting a longer phrase just because it includes the target word. Make the comparison case-sensitive or case-insensitive intentionally, and account for leading or trailing spaces if the source data may contain them.

For a row that should be deleted when either of two columns contains the text, test both cells within the same loop and delete the row once if either test succeeds. For a rule requiring both columns to match, combine the conditions with VBA’s And operator. When testing more columns, handle error-valued cells before converting them to strings, as the example does for column A.

For a whole-word match, a plain substring search may be too broad: searching for “art” also finds “carton.” You need a more specific test that checks the characters before and after the match, or a regular expression for complex text patterns. Decide how punctuation, hyphens, and word boundaries should behave before choosing that method; “whole word” can mean different things in real data.

Run the macro in Excel for Microsoft 365

Save a backup copy of the workbook, then open the VBA editor from Developer > Visual Basic. If the Developer tab is not visible, enable it in Excel Options under Customize Ribbon.
In the editor, choose Insert > Module, paste the code into the module, and edit the worksheet name, column, and search text.
Return to Excel and run the macro from Developer > Macros, or press Alt+F8 on Windows and select the macro. On a Mac, Option+F8 opens the Macro dialog in Excel; keyboard settings may affect how function-key shortcuts are entered.

Save the file as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code in it. Excel may require you to allow macros for a trusted file, according to your organization’s security settings. Do not enable macros in an unfamiliar workbook just to run its code.

Common problems and safer checks

No rows are deleted: Check the worksheet tab name, the column letter, the exact spelling of the search text, and whether the macro’s search is case-sensitive. Confirm the matching data is within the rows the last-row calculation reaches.
The macro deletes too much: A contains search matches text anywhere in the cell. Use an exact comparison if only a complete-cell match should qualify, and test first on a copy of the workbook.
Headers disappear: The loop boundary is too high for your layout. The sample begins at row 2 to preserve one header row; adjust the first data row only after confirming where your headers are.
Some records remain: Check whether the text uses a different spelling, contains unusual spaces, or is in another column. The sample skips error cells, and its boundary depends on column A.
The wrong workbook changes: Review the workbook reference and worksheet name. The sample uses ThisWorkbook, so it targets the workbook containing the macro.

A useful safety improvement is to first replace the delete line temporarily with a message or a highlight action, so you can inspect which rows meet the condition. Once the matching set is correct, restore the deletion line and run it on a saved copy. This is especially worthwhile when the search text is short or appears in many unrelated records.

When a filter or worksheet method is a better fit

VBA is useful when you need a repeatable cleanup, a custom text rule, or a macro that processes several sheets. It is not always the best first choice. For a one-time cleanup, use the column filter to display matching records, review them, and delete the visible rows. Filtering gives you a visual check before removal and avoids writing code, though the exact filter steps depend on whether the criterion is text, wildcard-based, or more complex.

Another cautious option is to add a helper column that labels rows as matches, review the labels, and then filter and remove those records. This preserves an audit trail until you choose to delete the rows. Unlike a macro, a worksheet formula or filter does not automatically make a permanent deletion just because a value matches. Choose based on whether the process needs to be repeatable and how important reviewability is.

Frequently Asked Questions

Does this macro delete a row if the cell contains part of a word?
Yes. InStr looks for the search text anywhere in the cell, so “art” matches “carton.” Use a whole-word check or a narrower search term if that is not intended.

How do I make the search case-sensitive?
Replace vbTextCompare with vbBinaryCompare in the InStr call. With binary comparison, uppercase and lowercase characters are treated as different.

Can I delete rows when a cell contains a number?
Yes, but decide whether you mean a numeric value or digits appearing in text. The example converts values to strings, so searching for “25” may match the number 25 and text such as “125 units.” For a numeric condition, compare the cell’s numeric value with the number instead.

Will the macro work if the cell contains a formula?
It checks the formula cell’s calculated value through Value2, not the formula text itself. A formula returning “cancelled” can match; a formula returning another result will not match that text.

Can I undo the deletion with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so deleted rows cannot be restored with Ctrl+Z. Save a backup before running the macro, especially on important data.

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.