Excel VBA: Find Text and Delete the Matching Row Safely

To find specific text in Excel and delete every row containing an exact match, run a VBA macro that checks the relevant column from bottom to top and deletes matching rows. The example below searches column A, treats upper- and lowercase letters as equivalent, and keeps row 1 as a header; change those settings to fit your sheet.

Macro to find text and delete matching rows

This macro looks for the complete cell value Discontinued in column A of a worksheet named Data. If a cell matches, it deletes that entire worksheet row, including its contents in other columns. Replace the sheet name, search term, and column reference before running it.

Option Explicit

Sub DeleteRowsWithExactText()
    Dim ws As Worksheet
    Dim searchText As String
    Dim lastRow As Long
    Dim r As Long

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

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

    ' Start at the bottom so deleted rows do not shift unsearched rows.
    For r = lastRow To 2 Step -1
        If Not IsError(ws.Cells(r, "A").Value2) Then
            If StrComp(CStr(ws.Cells(r, "A").Value2), _
                       searchText, vbTextCompare) = 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

The comparison is exact: a cell containing Discontinued matches, while Discontinued item does not. vbTextCompare makes the comparison case-insensitive, so DISCONTINUED also matches. The error check prevents a cell containing an Excel error value from causing a type-conversion problem.

How the macro works

The worksheet variable points to the tab named Data in the workbook that contains the macro. If the tab has a different name, edit that string exactly as it appears on the sheet tab. A misspelled or missing tab name causes a run-time error, so check the name before running the code.

The last-row calculation uses column A to find where the search data ends. The loop begins there and moves upward to row 2. That direction matters: deleting a row shifts the rows below it upward. If a macro moves downward instead, it can skip the row that slides into the position just deleted. Starting at the bottom avoids that problem.

The loop stops at row 2 because the example assumes row 1 contains column headings. If there is no header and data begins in row 1, change the loop endpoint from 2 to 1. If the search column has blank cells near the bottom, the last-row calculation still finds the last nonblank cell in that column; it does not search rows below that point.

Run the macro safely in Excel

Save a copy of the workbook before running any deletion macro. VBA deletions cannot be reversed with Ctrl+Z.
Open the workbook in the Excel desktop app. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros.
If the Developer tab is not visible on Windows, go to File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Preferences > Ribbon & Toolbar and enable Developer.
Choose Developer > Visual Basic. You can also open the Visual Basic Editor with Alt+F11 on Windows; on Mac, the shortcut can depend on keyboard settings, so the Developer ribbon command is a reliable route.
In the editor, choose Insert > Module, paste the code into the module, and update the sheet name, search text, or column as needed.
Return to Excel and run the macro from Developer > Macros: select DeleteRowsWithExactText and choose Run. Review the remaining rows and save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro.

Macros should be enabled only when you trust the workbook and its source. Do not lower Excel’s macro security settings globally just to run an unfamiliar file. For a one-time cleanup, a separate copy of the workbook is useful both as a backup and as a way to check that the intended rows were removed.

Search a different column or match part of a cell

To search column B instead of column A, change both occurrences of "A" in the code to "B": one is used to find the last row, and the other identifies the cell tested in each loop. Keep both references aligned. If the search column is changed in only one place, the macro may stop too early or inspect the wrong cells.

For a partial match, use InStr instead of StrComp. This separate version deletes a row when the search phrase appears anywhere in the column A cell. For example, searching for late would match both Late shipment and Customer contacted late.

Option Explicit

Sub DeleteRowsContainingText()
    Dim ws As Worksheet
    Dim searchText As String
    Dim lastRow As Long
    Dim r As Long
    Dim cellText As String

    ' Set the worksheet and phrase to find.
    Set ws = ThisWorkbook.Worksheets("Data")
    searchText = "late"

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Check from bottom to top and delete partial matches.
    For r = lastRow To 2 Step -1
        If Not IsError(ws.Cells(r, "A").Value2) Then
            cellText = CStr(ws.Cells(r, "A").Value2)
            If InStr(1, cellText, searchText, vbTextCompare) > 0 Then
                ws.Rows(r).Delete
            End If
        End If
    Next r
End Sub

In VBA code, the greater-than character in the InStr condition must be the ordinary > operator. In the HTML display above it is written as an entity; when copying the code, use > as the operator. The vbTextCompare argument makes this partial search case-insensitive. Use vbBinaryCompare instead if letter case must matter. With a partial search, choose the phrase carefully: a short term may match many more cells than intended.

Choose exact match, partial match, or Find

Exact matching is usually the safer choice for status labels, category names, or IDs stored consistently in one column. Partial matching is useful when cells contain longer descriptions and the target phrase can appear at different positions. Neither approach automatically ignores leading or trailing spaces. For instance, a cell containing Discontinued has a trailing space and will not equal the exact search string. If your data may contain extra spaces, inspect and clean a copy first, or deliberately adjust the comparison to trim ordinary spaces.

Excel’s Find dialog is convenient for locating a single occurrence: press Ctrl+F on Windows or Command+F on Mac, enter the text, and choose Find Next. But finding one cell is not the same as deleting every matching row. Repeating Find and deleting rows manually takes care, especially when there are many matches. A macro makes the repeated operation consistent, while a worksheet filter can be a useful no-code option when you want to inspect the matching records before removing them.

Common problems and important data details

Wrong sheet or column: The sheet name and column letter are specified in the code, not inferred from the active selection. Confirm them before running the macro.
Unexpected matches or missed rows: The first macro checks the whole cell value, not a substring. Hidden spaces, inconsistent spelling, and different punctuation can prevent an apparent match.
Formula cells: The macro reads Value2, which returns the cell’s calculated value rather than its formula text. It therefore compares the displayed result stored as a value, not the formula that produced it.
Errors in the search column: Cells returning values such as #N/A are skipped by the error check. They are not converted into text and are not treated as matches.
Entire worksheet rows are removed: ws.Rows(r).Delete deletes the worksheet row, not just the matching cell. This can remove unrelated information elsewhere on that row, so confirm the sheet is laid out as one coherent table.
Excel tables and neighboring data: If the records are in an Excel Table or other data sits beside them, deleting whole worksheet rows may affect more than the table. Filter the table and delete only its matching table rows, or adapt the VBA to delete table rows specifically.

A frequent misconception is that Ctrl+Z can restore a macro’s deletions. Excel does not place VBA actions on the normal undo stack, so pressing Ctrl+Z after the macro will not bring those rows back. The reliable safeguards are to save a separate copy, test on a small sample, and verify the target column and match rule before running the full cleanup.

No-macro alternative: filter before deleting

If the operation is a one-off and you want to inspect results, use a filter instead of VBA. Select a cell in the data range, choose Data > Filter, open the filter dropdown for the target column, and select the matching text. For a partial phrase, use the text filter option that contains the phrase. Review the visible rows before selecting and deleting them. Filtering helps make the selection visible, but take care to delete entire worksheet rows only when that is intended; deleting cell contents alone leaves blank records in place.

For repeated cleanup, VBA is more efficient once the criteria are precise. For a small dataset, the filter approach is often easier to audit because you can see the rows selected before deletion. If the workbook uses an Excel Table, its built-in filter and table-row deletion behavior may be preferable to deleting entire worksheet rows, particularly when unrelated data shares the same worksheet.

Frequently Asked Questions

Does the macro delete every row containing the text?
Yes. It checks each used row in the chosen column and deletes every match it finds. The exact-match macro compares the complete cell value; the partial-match version looks for the phrase anywhere within the cell.

How can I make the search case-sensitive?
In the exact-match macro, replace vbTextCompare with vbBinaryCompare. In the partial-match macro, make the same change in the InStr call. The binary comparison distinguishes uppercase and lowercase letters.

Can I search more than one column?
Yes, but the example checks only one column. You can extend the loop to inspect a specified range of columns on each row, then delete that row once if any cell meets the criterion. Decide whether the match must occur in one particular column or anywhere in the record before changing the code.

Will this work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app for Windows or Mac to use VBA; the web version can still display and edit many workbook contents.

Can I undo the deleted rows with Ctrl+Z?
No. VBA actions generally clear Excel’s ordinary undo history, and macro deletions cannot be restored with Ctrl+Z. Close without saving if appropriate, or restore the saved backup copy.

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.