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

Introduction

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.
The 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:

Sample WorkBook

A sample workbook is attached so that users can experiment with the four different Find scenarios and the post processing option.
DelRow.xls
 

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
                              Do
                                  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
                          Else
                              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)
                                      Else
                                          Set rng3 = cel2.EntireRow
                                      End If
                                  End If
                              Next
                              If Not rng3 Is Nothing Then rng3.EntireRow.Delete
                          End If
                      
                          With Application
                              .ScreenUpdating = True
                              .Calculation = lAppCalc
                          End With
                      
                      End Sub
                      

Select all 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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=