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
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
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.
Const strText As String = "FindMe"
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
lAppCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
'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
'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
If Not rng3 Is Nothing Then rng3.EntireRow.Delete
.ScreenUpdating = True
.Calculation = lAppCalc
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!