<

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

Published on
13,717 Points
3,517 Views
7 Endorsements
Last Modified:
Approved

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

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
7
Comment
Author:Dave
1 Comment
 
LVL 47

Expert Comment

by:aikimark
@Dave

I was thinking about an article like yours while answering this question.
http://www.experts-exchange.com/Q_26466861.html

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?
0

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month