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.

Remove rows based on cell value with Find and Replace function

Remove rows based on cell value with VBA code

Remove rows based on cell value with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Remove rows based on cell value with Find and Replace function

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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.


arrow blue right bubble Remove rows based on cell value with VBA code

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 doc-multiply-calculation-3button 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.


arrow blue right bubble Remove rows based on cell value with VBA code

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

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 visitSelect Specific Cells.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

 

Comments  

0#Alan2013-11-19 18:59
Fantastic! That worked a treat, I altered it slightly to suit what I needed but pure genious. Oh VBA Version.
Reply | Reply with quote | Quote
0#Bobby2013-12-10 01:12
Any way to modify the If (cell.Value) = "Apple" to include multiple values?
Reply | Reply with quote | Quote
0#Art2013-12-12 14:25
Bobby, try this, it should get what you want:
If (cell.Value) = "Apple" OR (cell.Value) = "Monday"
Reply | Reply with quote | Quote
0#April2013-12-23 23:39
Is there anyway to have the value recognize a value whether is > or = to a certain value?
Reply | Reply with quote | Quote
+1#Claire2013-12-16 06:44
Hey - thanks so much for the script. I was wondering if you can use wildcards so you can select anything within a cell that matches rather than a specific item? I've tried using ** wildcards but it doesn't seem to do anything. Here is what I have:

Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("B6:B20"), ActiveSheet.UsedRange)
For Each cell In rng
If cell.Value Like "*WORDTODELETE*" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
Reply | Reply with quote | Quote
0#Jim Mc2014-01-08 20:12
Heartfelt thanks - it's always amusing when the Marketing guy tries to write code, and this info was super helpful. I was unable to get the code to span multiple columns ("AA2:AA3000" works, "AB2:AB3000" works, but "AA2:AB3000" doesn't work. There's no Earth-shattering Kaboom - it just does nothing. I've solved it (amateurishly, I suppose) by running several macros in sequence, but there's probably a more elegant way. Thanks again for your help, Jim (Melville, NY)
Reply | Reply with quote | Quote
+2#H man2014-01-09 13:24
I get:

run-time error '13':
Type mismatch

Debugging highlights the If (cell.Value) = "FALSE" _
Then
section.

I'm totally lost in MVB, any help would be appreciated.
Reply | Reply with quote | Quote
+1#Adam2014-02-28 04:15
Lets say I put the below code in to delete all of my rows that contain apple but then I want it to continue once that is done and delete all the rows that contain banana? I tried to just duplicate the code but it seems to stop after apple.

thanks.

Sub Delete_Rows()
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" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
Reply | Reply with quote | Quote
0#Joe Nelson2014-04-15 02:52
I just wanted to say thank you. This worked like a charm.
Reply | Reply with quote | Quote
0#Mira2014-06-04 17:53
Definitely awsome! Appreciate for sharing!!!
Reply | Reply with quote | Quote
0#Aleardo2014-06-11 19:03
Thanks, this is great!
Quick comment, I tried the find and replace function in excel 2010.
In order to select all the found results I had to use Ctrl +A instead of Alt + A.
Reply | Reply with quote | Quote
0#Tee2014-06-17 01:10
Hi,
What is the easiest way to delete rows that do NOT contain "Apple" please?

Thanks
Reply | Reply with quote | Quote
0#Leo2014-06-22 00:33
Hi,
Any way to write it to delete a row, with an OR exception? I.e- delete rows that are duplicates, but ignore a certain value. I want to delete duplicates, except where the column in question contains a blank b/c that column doesn't have valid data yet. I ran as you have, but I ended deleting rows with a blank in the column in question, so I can't use it as it is.
Thanks
Reply | Reply with quote | Quote
0#smj20132014-07-13 01:14
Thank you for the VB Script. I can not wait to try this. Question:Is there anyway to mark the columns for deletion prior to deleting them and/or copy to a separate tab in the workbook? In addition is there a way to run this script run on multiple workbooks/files at one time?
Reply | Reply with quote | Quote

Add comment


Security code
Refresh