Our community of experts have been thoroughly vetted for their expertise and industry experience.
Browse All Articles > 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
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.
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:
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.
A sample workbook is attached so that users can experiment with the four different Find scenarios and the post processing option. DelRow.xls
Option ExplicitConst 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 WithEnd Sub