Skip to main content

How to find and delete multiple matches at once in Excel?

When working with large data in Excel, it's crucial to be able to quickly find the information you need at any particular moment. And that’s when you need the Find and Replace feature, which helps you to find and highlight specific values or formats across the active sheet or the whole workbook and then do what you want with the results, say, delete or replace them. However, with Excel’s Find and Replace, you can only find one value at a time. In this tutorial, I will introduce a quick way to find multiple different values and delete them at once in Excel.


Find and delete multiple matches at once with VBA

We have created two VBA codes to help you find multiple different values in the selected range or across multiple worksheets and delete them immediately. Please follow the steps below and run the code according to your needs.

1. In your Excel, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module. Then copy either of the following VBA codes to the Module window.

VBA code 1: Find and delete multiple matches in the selected range at once

Sub FindAndDeleteDifferentValues_Range()
'Updated by ExtendOffice 20220823
Dim xRg As Range
Dim xFindStr As String
Dim xFindRg As Range
Dim xARg As Range
Dim xURg As Range
Dim xFindRgs As Range
Dim xFAddress As String
Dim xBol As Boolean
Dim xJ

xArrFinStr = Array("sales", "9", "@") 'Enter the values to delete, enclose each with double quotes and separate them with commas

On Error Resume Next
Set xRg = Application.InputBox("Please select the search scope:", "Kutools for Excel", , Type:=8)
If xRg Is Nothing Then Exit Sub

xBol = False
For Each xARg In xRg.Areas
    Set xFindRg = Nothing
    Set xFindRgs = Nothing
    Set xURg = Application.Intersect(xARg, xARg.Worksheet.UsedRange)
    For Each xFindRg In xURg
        For xJ = LBound(xArrFinStr) To UBound(xArrFinStr)
            If xFindRg.Text = xArrFinStr(xJ) Then
                xBol = True
                If xFindRgs Is Nothing Then
                    Set xFindRgs = xFindRg
                Else
                    Set xFindRgs = Application.Union(xFindRgs, xFindRg)
                End If
            End If
        Next
    Next
    If Not xFindRgs Is Nothing Then
        xFindRgs.ClearContents
    End If
Next
If xBol Then
    MsgBox "Successfully deleted."
Else
     MsgBox "No results found."
End If
End Sub

Note: In the snippet xArrFinStr = Array("sales", "9", "@") in the 13th row, you should replace "sales", "9", "@" with the actual values you want to find and delete, remember to enclose each value with double quotes and separate them with commas.


VBA code 2: Find and delete multiple matches across multiple sheets at once

Sub FindAndDeleteDifferentValues_WorkSheets()
'Updated by ExtendOffice 20220823
Dim xRg As Range
Dim xFindStr As String
Dim xFindRg As Range
Dim xARg, xFindRgs As Range
Dim xWShs As Worksheets
Dim xWSh As Worksheet
Dim xWb As Workbook
Dim xURg As Range
Dim xFAddress As String
Dim xArr, xArrFinStr
Dim xI, xJ
Dim xBol As Boolean
xArr = Array("Sheet1", "Sheet2") 'Names of the sheets where to find and delete the values. Enclose each with double quotes and separate them with commas
xArrFinStr = Array("sales", "9", "@") 'Enter the values to delete, enclose each with double quotes and separate them with commas
'On Error Resume Next
Set xWb = Application.ActiveWorkbook
xBol = False
For xI = LBound(xArr) To UBound(xArr)
    Set xWSh = xWb.Worksheets(xArr(xI))
    Set xFindRg = Nothing
    xWSh.Activate
    Set xFindRgs = Nothing

    Set xURg = xWSh.UsedRange
    Set xFindRgs = Nothing
    For Each xFindRg In xURg
        For xJ = LBound(xArrFinStr) To UBound(xArrFinStr)
            If xFindRg.Text = xArrFinStr(xJ) Then
                xBol = True
                If xFindRgs Is Nothing Then
                    Set xFindRgs = xFindRg
                Else
                    Set xFindRgs = Application.Union(xFindRgs, xFindRg)
                End If
            End If
        Next
    Next
    If Not xFindRgs Is Nothing Then
        xFindRgs.ClearContents
    End If
Next

If xBol Then
    MsgBox "Successfully deleted."
Else
     MsgBox "No results found."
End If
End Sub
Note:
  • In the snippet xArr = Array("Sheet1", "Sheet2") in the 15th row, you should replace "Sheet1", "Sheet2" with the actual names of the sheets where you want to find and delete the values. Remember to enclose each sheet names with double quotes and separate them with commas.
  • In the snippet xArrFinStr = Array("sales", "9", "@") in the 16th row, you should replace "sales", "9", "@" with the actual values you want to find and delete, remember to enclose each value with double quotes and separate them with commas.

3. Press F5 to run the VBA code. Note: If you use the VBA code 1, a dialog box will pop up asking you to select the range where to find and delete values. You can also click on a sheet tab to select the whole sheet.

4. The dialog box as shown below pops up telling you that the code has deleted the specified matches. Click OK to close the dialog.

5. The specified values have been deleted at once.


Find and delete multiple matches at once with Select Specific Cells feature

Kutools for Excel offers the Select Specific Cells feature to find values that meet one or two conditions you set at once, please read on to find out how to quickly find and delete multiple values at once.

1. On the Kutools tab, in the Editing group, click Select > Select Specific Cells.

2. In the pop-up Select Specific Cells dialog box, please do as follows:
  • In the Select cells in this range box, click the range-selecting button at right to specify the range from where to find and delete values. Note: To search across the whole sheet, click on the sheet tab.
  • In the Selection type section, select the Cell option.
  • In the Specific type section, set at most two conditions:
    • Click on the drop-down arrow at left side to select a relationship you need, such as Contains, Equals, Less than, Begins with, etc.
    • Type the value in the corresponding box according to your needs.
    • Specify the relationship between the two conditions (if there are): And or Or.

3. Click Ok to select the cells that meet the condition(s) you set. A dialog box pops up telling you how many cells were found and selected.

4. Click OK. Now, press the DELETE key to delete the selected values at once.

Note: To use the Select Specific Cells feature, you should have Kutools for Excel installed in your computer. If you do not have Kutools installed, click here to download and install. The professional Excel add-in offers a 30-day free trial with no limitations.


Find and delete multiple matches at once In Excel


Related articles

Find, Highlight, Filter, Count, Delete Duplicates In Excel

In Excel, duplicate data occurs time after time when we record data manually, copy data from other sources, or for other reasons. Sometimes, the duplicates are necessary and useful. However, sometimes the duplicate values lead to errors or misunderstanding. Here, this article will introduce methods to quickly identify, highlight, filter, count, delete duplicates by formulas, conditional formatting rules, third-party add-ins, etc. in Excel.

How To Compare Two Columns And Delete Matches In Excel?

If you have two columns/lists including some duplicates, now you want to compare them and find out the matches, the delete them, how can you quickly solve? In this article, I introduce different ways to help you handle it in Excel.

How To Delete All But Selected Ranges In Excel?

In Excel, we can quickly and directly delete the selected ranges, but have you ever tried to delete other cell contents except the selected ranges? This article, I will introduce some tricks for you to solve this task in Excel quickly and easily.

How To Delete All Pictures In A Range Of Cells?

If there are multiple pictures populated into your worksheet, now, you want to delete some of them in a specified range, how do you deal with it quickly?

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations