Using Find and FindNext to efficiently delete any rows that contain specific text



This Article provides VBA code for an efficient method to delete rows that contain specific text strings. The code provides options for:

1) Case insensitive or Case sensitive searches
2) Matching a text string to either the exact cell contents or a partial content match
3) Post processing a successful found range with a further conditional test

Using the code

  1. Copy the code at the bottom of this Article
   2. Open any workbook.
   3. Press Alt + F11 to open the Visual Basic Editor (VBE).
   4. From the Menu, choose Insert-Module.
   5. Paste the code into the right-hand code window.
   6. Close the VBE, save the file if desired.

   In xl2003 go to Tools-Macro-Macros and double-click  ColSearch_DelRows
   In xl2007 click the Macros button in the Code group  of the Developer tab, then click ColSearch_DelRows in the list box.
Please note that this code must be run from a regular VBA Code Module - else the code will cause an error if users try to run it from the ThisWorkbook or Sheet Code panes given the usage of Const. It is worth noting that the ThisWorkbook and Sheet code sections should be reserved for Event coding only, "normal" VBA should be run from standard Code Modules.

Optons to delete rows based on cell content

Deleting rows based on the presence (or more rarely the non-presence) of specific text strings is one of the most frequently requested problems posted to the online help forums.  The three commonly used techniques are:

   1. Looping cell-by-cell through a range
   2. Using AutoFilter
   3. Using the Find Method

Looping through cells is simple to code but terribly inefficient other than for small ranges. There are always superior options available in the coders armoury, readers may find my Article on using Variant Arrays rather than loops useful,   Using Variant Arrays in Excel VBA for Large Scale Data Manipulation.

Using AutoFilter to insert a formula column that applies a criteria for a VBA filter is a very useful and speedy technique. I use this approach when I am testing for the non-appearance of data when it is expected, ie a "negative" test.

For "positive" testing, I prefer to work with a range that holds the cells containing the matched data, the Find Method provides this range simply and efficiently.

Find Method

The screenshot below is taken directly from VBA help.
Find screenshotThe arguments highlighted in green, LookAt and MatchCase, are varied in the code sample to provide
a) Search for text anywhere within a cell without regard to case (LookAt:=xlPart, MatchCase:=False)
b) Search for text anywhere within a cell, case sensitive (LookAt:=xlPart, MatchCase:=True)
c) Search for an exact text match on the entire cell contents without regard to case (LookAt:=xlWhole, MatchCase:=False)
d) Search for an exact text match on the entire cell contents,  case sensitive (LookAt:=xlWhole, MatchCase:=True

Please note that the code currently is running with (a), the other three scenarios have been commented out.

There is an important caveat noted in VBA help, "The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method"

This means that:
1) These settings should be specified each time a Find is run if these settings are important to the way the Find examines data.  In this case I have highlighted LookAt with a red border, as for this example we need to explicitly set whether the Find matches the entire or partial cell contents. Else the Find Method will inherit the prior Find settings - which can be disastrous when deleting rows with VBA.

2) Less obviously, the same code may operate differently on different computers depending on the inherited Find settings. Which can lead to some confusion, i.e., "The code runs fine for me, I can't tell why it fails for you!"

The Find Method runs a single search.  To run a complete search over a specific area coders need to combine FindNext with the Find. A simple unique string address - StrFirstAddress - can be used to mark the point where the FindNext Method has returned to the initial cell position located by the Find.

Understanding the code

There are 6 major portions in the sample code:

The user is prompted to select a range for the Find (the current selection provides a default range to search)

A Boolean flag is used to indicate whether there will be further processing of the results range. This flag is set to False in this code sample, so the code will simply process the results range as is.

The user sets the Find scenario with the LookAt and MatchCase settings provided by the "live" code from options (a), (b), (c), or (d).

The Find / FindNext Methods are use to locate the cells that contain the text stored by the string variable, StrText.

A Range variable, rng2 is used to store the entire row where a valid match is found (the entire row is stored rather than individual cells as the code will crash if the user tries to delete a row containing multiple cells that are not continuous).

The post processing flag dictates whether any matching rows are immediately deleted (the bParseString = False option is tested first as it is more likely), or whether further conditions are to be tested. The code provides a sample that adds a condition (if bParseString = True) that any row to be deleted must also contain "Duplicate" in column A of the same row for a valid deletion.

Sample WorkBook

A sample workbook is attached so that users can experiment with the four different Find scenarios and the post processing option.
Option Explicit
                      Const strText As String = "FindMe"
                      Sub ColSearch_DelRows()
                          Dim rng1 As Range
                          Dim rng2 As Range
                          Dim rng3 As Range
                          Dim cel1 As Range
                          Dim cel2 As Range
                          Dim strFirstAddress As String
                          Dim lAppCalc As Long
                          Dim bParseString As Boolean
                          'Get working range from user
                          On Error Resume Next
                          Set rng1 = Application.InputBox("Please select range to search for " & strText, "User range selection", Selection.Address(0, 0), , , , , 8)
                          On Error GoTo 0
                          If rng1 Is Nothing Then Exit Sub
                          'No further processing of matches
                          bParseString = False
                          With Application
                              lAppCalc = .Calculation
                              .ScreenUpdating = False
                              .Calculation = xlCalculationManual
                          End With
                          'a) match string to entire cell, case insensitive
                          Set cel1 = rng1.Find(strText, , xlValues, xlWhole, xlByRows, , False)
                          'b) match string to entire cell, case sensitive
                          'Set cel1 = rng1.Find(strText, , xlValues, xlWhole, xlByRows, , True)
                          'c)match string to part of cell, case insensititive
                          ' Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , False)
                          'd)match string to part of cell, case sensititive
                          ' Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , True)
                          'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
                          If Not cel1 Is Nothing Then
                              Set rng2 = cel1
                              strFirstAddress = cel1.Address
                                  Set cel1 = rng1.FindNext(cel1)
                                  Set rng2 = Union(rng2.EntireRow, cel1)
                              Loop While strFirstAddress <> cel1.Address
                          End If
                          'Further processing of found range if required
                          'This sample looks to delete rows that contain the text in StrText AND where column A contains "Duplicate"
                          If Not bParseString Then
                              If Not rng2 Is Nothing Then rng2.EntireRow.Delete
                              For Each cel2 In rng2.Rows
                                  If Cells(cel2.Row, "A").Value = "Duplicate" Then
                                      If Not rng3 Is Nothing Then
                                          Set rng3 = Union(rng3, cel2.EntireRow)
                                          Set rng3 = cel2.EntireRow
                                      End If
                                  End If
                              If Not rng3 Is Nothing Then rng3.EntireRow.Delete
                          End If
                          With Application
                              .ScreenUpdating = True
                              .Calculation = lAppCalc
                          End With
                      End Sub

Open in new window

If you liked this article and want to see more from this author, please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!

Comments (1)

aikimarkGet vaccinated; Social distance; Wear a mask
Top Expert 2014


I was thinking about an article like yours while answering this question.

One of the points I was going to make related to deleting items in lists and arrays as well as rows in a worksheet.  One of the dangers that programmers face when doing deletions is deleting items in front-to-back order, which changes the list as it shrinks.  Depending on the code, rows might be skipped or incorrect rows might be deleted.

Thank you for showing the partial match parameter.  Which parameter configuration would you use for wildcard matches?

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.