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.
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, Find and Replace function is very powerful and useful, and now you can use it to remove rows based on a cell value, too.
1. Press Ctrl + F to open Find and Replace dialog, and then type Apple (Apple is the cell value you want to base on) into the Find what text box. See screenshot:
2. Then click Find All button in the dialog, and press Alt + A to select all found results. See screenshot:
3. Click Close to close the dialog. And you can see all the cells whose contents is apple are selected. The right click to open the context menu, and click Delete…. See screenshot:
4. In the Delete dialog, check Entire row, then click OK. Then all the rows including Apple are removed.
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
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 button to run the code, and a dialog displays for you to select a range to work. See screenshot:
4. Then click OK, and another dialog displays for you to select the value text you want to delete, see screenshot:
5. Then click OK, you can see the result:
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 > 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 .
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...