How to remove rows based on cell value in Excel?
To quickly delete or remove multiple rows based on cell value in Excel, you may need to select those entire rows containing specific cell value first, and then go to remove them. It seems that there isn’t a quick way to delete rows based on cell value but using VBA code. Here are some quick tricks to help you.
Classic Menu for Office: Bring Back Old Look to Office 2010 and 2013 (includes Office 365).
Office Tab: Bring Tabbed Editing and Browsing Features to Office, Just Like Chrome, Firefox, Internet Explorer 8/9/10.
With the following VBA code, you can quickly delete the rows with certain cell value, please do as the following steps:
1. Select the range that you want to delete the specific row.
2. Click Developer>Visual Basic, a new Microsoft Visual Basic for applications window will be displayed (or you can press the shortcut key Alt + F11 to open the window), click Insert > Module, and input the following code into the Module:
VBA: Remove entire rows based on cell value
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:C20"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Apple" _
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
On Error Resume Next
Note: From the above code, the Range (“A1:C20”) and If (cell.Value) = "Apple" are variable. You can modify the code as your need.
But as for some Excel beginners, VBA code is difficult for them, here is an easy way to help them.
If you have installed Kutools for Excel, Select Specific Cells function can help you quickly delete the rows with specific value. Please do as follows:
1. Highlight the range you want to use.
2. Click Kutools > Select Tools > Select Specific Cells. See screenshot:
3. And then a Select Specific Cells dialog box will appear, check Entire row from Selection type, and choose Contains from Specific type dropdown list, enter the specified value in the box. See screenshot:
4. Click OK or Apply. And all of the rows with the specific value have been selected.
5. Then put the cursor at the selected row, right-click to choose Delete from the menu. See screenshot:
And the entire rows containing specific value will be removed. If you want to know more about this function, please visit Select Specific Cells.