Note: The other languages of the website are Google-translated. Back to English
English English

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?


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations