Deleting values in multiple cells is not a problem when the cells are continuous or located in a small spreadsheet. However, deleting cell values that are scattered around a large, complex spreadsheet is considerably more tedious unless you use Excel's Find All tool. This tool searches the spreadsheet for multiple instances of entered text and allows you to quickly select and delete them all. The tool also offers the option of selecting just text values or also formulas relying on those values.
Click and drag your mouse across multiple cells to restrict the search to only the selected cells. If you wish to search the entire spreadsheet, do not select multiple cells.
Hold the "Ctrl" key and press "F" to open the Find tab in the Find and Replace window.
Enter the text you wish to find in the "Find What" field.
Click the "Options" button to restrict or broaden the search. Check "Match Case" to find only values matching your exact capitalization. Check "Match Entire Cell Contents" to avoid finding text located within longer text. Click the "Look In" drop-down menu and select "Values" to avoid also selecting formulas that reference the searched value.
Click "Find All" to open a window displaying references to all cells matching your search criteria.
Hold the "Ctrl" key and press "A" to select all the referenced cells.
Click "Close" to close the Find and Replace window.
Press "Delete" to delete the contents of all cells matching your search criteria.
Recently I had some data from a website that was poorly formatted, but I needed to get it into a spreadsheet to work with. When I copied the data into the spreadsheet, I needed to remove all of the rows that contained certain irrelevant data, such as repeated header fields. Other use cases might require you to delete any rows containing someone’s name, a location, or some other information
to trim the excess data from your sheet. All rows containing the data you wanted to remove should be gone now!
You can also remove an entire column of data that contains certain information in a similar manner. To remove the entire column, simply select the Entire Column option in the last step above.
As with any data modification, be sure to have a copy of your data saved elsewhere as a backup just in case you accidentally remove some important information. This will ensure that when trying to remove all rows containing certain data, you do so in the safest possible way.
Remove Certain Rows Containing Certain Data with VBA
In the developer tab, go to Visual Basic as normal and create a module like the one below. Change and amend the code to your needs and then simply run the module on your sheet.
Sub Delete_All_Rows_IF_Cell_Contains_Certain_String_Text[]
Dim lRow As Long
Dim iCntr As Long
lRow = 1000
For iCntr = lRow To 1 Step -1
If Cells[iCntr, 3].Value = "Certain data to delete here" Then
Rows[iCntr].Delete
End If
Next
End
Number “3” in the ‘If Cells [iCntr, 3].Value represents the third
column [C]
lRow = 1000 means it will check the first 1000 rows.