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 SubNote: 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- 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.
- 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.
at right to specify the range from 
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
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
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!
