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.
Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch sum/average/max/min/count the occurrences of each items in a column (the Fruite Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below screenshot shown:
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
In Excel, you can apply the powerful Find and Replace feature to remove rows based on a certain cell value easily. Please do as follows:
1. Select the range where you will remove rows based on certain value, and open the Find and Replace dialog box with pressing the Ctrl + F keys simultaneously.
2. In the Find and Replace dialog box, please type the certain cell value (in our case, we enter the Soe) into the Find what box, and click the Find All button. See the first screenshot below:
3. Select all searching results at the bottom of Find and Replace dialog box, and close this dialog box.
Note: You can select one of searching result, and then Ctrl + A keys to select all found results. See the second screenshot above.
And then you can see all the cells whose contents is the certain value are selected.
4. Go ahead and right click selected cells and select the Delete from the right-clicking menu. And then check the Entire row option in the popping up Delete dialog box, and click the And then you can see all the cells whose contents is the certain value are selected. button. See screenshots below:
And then entire rows have been deleted based on the certain value already.
With the following VBA code, you can quickly delete the rows with certain cell value, please do as the following steps:
1. Press the Alt + F11 keys at the same time to open Microsoft Visual Basic for applications window,
2. Click Insert > Module, and input the following code into the Module:
VBA: Remove entire rows based on cell value
Sub DeleteRows() 'Updateby20140314 Dim rng As Range Dim InputRng As Range Dim DeleteRng As Range Dim DeleteStr As String xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2) For Each rng In InputRng If rng.Value = DeleteStr Then If DeleteRng Is Nothing Then Set DeleteRng = rng Else Set DeleteRng = Application.Union(DeleteRng, rng) End If End If Next DeleteRng.EntireRow.Delete End Sub
3. Then click the Run button to run the code.
4. In the popping up dialog box, please select the range where you will remove rows based on the certain value, and click the OK button.
5. In another dialog box, please type the certain value you will remove rows based on, and clicks the OK button. See screenshot:
And then you will see entire rows have been deleted based on the specified value already.
If you have installed Kutools for Excel, its Select Specific Cells feature can help you quickly delete the rows with specific value. Please do as follows:
1. Select the range where you will remove rows based on the certain value, and lick Kutools > Select > Select Specific Cells. See screenshot:
2. In the opening Select Specified Cells dialog box, please check Entire row option, select Contains from Specific type drop down list, enter the specified value into following box, and click the Ok button. See above screenshot:
After applying this feature, a dialog box will pop out and show you how many cells have been found based on the specified criteria. Please click the OK button to close it.
3. Now entire rows with the certain value are selected. Please right click any selected rows, and select the Delete from the right-clicking menu to delete these rows. See screenshot below:
Note: This Select Specific Cells feature supports to delete rows based on on or two certain values. For deleting rows based on two specified values, please specified another value in tHE Specific type section of Select Specific Cells dialog box as following screenshot shown:
In some cases, you may need to remove rows based on multiple cell values from another column/list in Excel. Here I will introduce Kutools for Excel's Compare Ranges utility to solve it quickly with ease.
1. Click Kutools > Compare Ranges to open the Compare Ranges dialog box.
2. In the opening Compare Ranges dialog box, please do as follows (see screenshot):
(1) In the Find values in box, please select the column where you will find the certain values and then delete rows;
(2) In the According to box, please select the column/list with multiple values you will delete rows based on;
Note: If two specified columns contain the same header, please check the My data has headers option.
(3) In the Find section please check the Same Values option;
(4) Check the Select entire rows option at the bottom of opening dialog box.
3. Click the Ok button to apply this utility. And then a dialog box comes out and shows how many rows have been selected. Just click the OK button to close it.
And then all rows contain the one of multiple values in the specified column have been selected.
4. Click Home > Delete > Delete Sheet Rows to delete all selected rows.
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...