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 one cell value with Find and Replace function

Remove rows based on one cell value with VBA code

Remove rows based on one or two cell values

Remove rows based on multiple cell values

Easily select entire rows based on a list, and then delete them easily

With Kutools for Excel's Select Same & Different Cells feature, you can easily compare two columns of values, and then select the entire rows based on the same values or different values as below screenshot shown. And it will be easily to delete these rows after selecting in Excel. Click for 60-day free trial!

ad select entire rows by multiple values


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


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.


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. 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.


arrow blue right bubble Remove rows based on one or two cell values with Kutools for Excel

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:

Kutools for Excel - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

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:

Free Trial Kutools for Excel Now


arrow blue right bubbleRemove rows based on multiple cell values with Kutools for Excel

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.

Kutools for Excel - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

1. Click Kutools > Select > Select Same & Different Cells to open the Select Same & Different Cells dialog box.

2. In the opening Select Same & Different Cells 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;
(3) In the Based on section, please check the Each row option;
(4) In the Find section, please check the Same Values option;
(5) Check the Select entire rows option at the bottom of opening dialog box.

Note: If two specified columns contain the same header, please check the My data has headers option.

Free Trial Kutools for Excel Now

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.


arrow blue right bubbleDemo: remove rows based on one or multiple cell values in Excel

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +1 Alan
Fantastic! That worked a treat, I altered it slightly to suit what I needed but pure genious. Oh VBA Version.
2013-11-19 18:59 Reply Reply with quote Quote
Permalink +1 Bobby
Any way to modify the If (cell.Value) = "Apple" to include multiple values?
2013-12-10 01:12 Reply Reply with quote Quote
Permalink 0 Art
Bobby, try this, it should get what you want:
If (cell.Value) = "Apple" OR (cell.Value) = "Monday"
2013-12-12 14:25 Reply Reply with quote Quote
Permalink 0 April
Is there anyway to have the value recognize a value whether is > or = to a certain value?
2013-12-23 23:39 Reply Reply with quote Quote
Permalink +3 Claire
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
2013-12-16 06:44 Reply Reply with quote Quote
Permalink -1 Jim Mc
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-shatterin g 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)
2014-01-08 20:12 Reply Reply with quote Quote
Permalink +3 H man
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.
2014-01-09 13:24 Reply Reply with quote Quote
Permalink +1 Adam
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
2014-02-28 04:15 Reply Reply with quote Quote
Permalink 0 Joe Nelson
I just wanted to say thank you. This worked like a charm.
2014-04-15 02:52 Reply Reply with quote Quote
Permalink 0 Mira
Definitely awsome! Appreciate for sharing!!!
2014-06-04 17:53 Reply Reply with quote Quote
Permalink 0 Aleardo
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.
2014-06-11 19:03 Reply Reply with quote Quote
Permalink 0 Tee
Hi,
What is the easiest way to delete rows that do NOT contain "Apple" please?

Thanks
2014-06-17 01:10 Reply Reply with quote Quote
Permalink 0 Leo
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
2014-06-22 00:33 Reply Reply with quote Quote
Permalink 0 smj2013
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?
2014-07-13 01:14 Reply Reply with quote Quote
Permalink 0 gabz
thank.. it helps me a lot...
2014-08-18 09:14 Reply Reply with quote Quote
Permalink 0 alex2
I'd like to delete all rows where column1 = "Apple" AND column3 = "green". please?
2014-10-03 14:14 Reply Reply with quote Quote
Permalink 0 Finidi
Im not sure how to do this using macro's or so, but one option that would do its job just fine is to add an extra colum that tests for the one colum to be "apple" and the other to be "green" and then let it give a simple "yes" or "no" value. than use either of the above options to search for the value "yes" and remove rows based on that instead of 2 seperate values.
2014-10-08 18:20 Reply Reply with quote Quote
Permalink 0 Victor
i have a list of stores in one document. I have a report that returns thousand of lines. the store number is always in Column A. Is there a code/macro that will search the report and delete all columns with store numbers from the store list. thinking a vlookup and "Do While" are needed.
2014-10-08 20:02 Reply Reply with quote Quote
Permalink 0 Joan K
Hello and thank you very much for the VBA code. I have one question.

Is it possible to make the code the way that it is possible to choose several cell values to delete at the same time? For example choose Apple and Emily at the same time and delete them?
I appreciate you work and looking forward to your response.

Joan K
2015-01-20 11:01 Reply Reply with quote Quote
Permalink 0 DavidGough
How can I specify the range A3:D3000 (ie. I don´t want the user to select the range)? How can I make the Delete String a cell reference, ex. G1?
2015-03-16 13:57 Reply Reply with quote Quote
Permalink 0 DavidGough
is it possible to compare two columns and then delete? is in range A3:D3000 if cell A=x and cell B=y then delete row?
2015-03-16 13:58 Reply Reply with quote Quote
Permalink 0 angel baby
What a great. thank so all so much. :)
2015-06-26 03:10 Reply Reply with quote Quote
Permalink 0 JonS
Is there anyway to have the value recognize a value that has expired, such as. All rows that show an expiration date within a specific column will be automatically removed and then the rows that were deleted will be filled by moving the rows beneath. Filling gaps.
2015-07-02 14:56 Reply Reply with quote Quote
Permalink 0 Meredith
Just what I needed, thanks! :D
2015-07-23 21:15 Reply Reply with quote Quote
Permalink 0 Moe
Hello, many thanks for the code. But say I want to delete one row containing "Apple". And not all of the rows containing it. Let's just say the last one, or a random one, doesn't really matter, just one. Many thanks in advance! :-)
2015-08-13 06:37 Reply Reply with quote Quote
Permalink 0 Milon
Great it work thank you so much
2015-08-19 19:28 Reply Reply with quote Quote
Permalink 0 Clay
How can i delete selected cell that i want based on value that i entered for example :
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4

When i entered in some cell for example "2" then 2 row will be deleted from A3:D4. If i entered "1" then 1 row will be deleted from A4:D4. if i entered "3" then 3 row will be deleted from A2:D4
2015-08-21 11:18 Reply Reply with quote Quote
Permalink 0 NC Arch
Trying to find a delete Function that will delete entire designated rows automatically, based on certain values or certain text contained in other cell(s), using only automated formulas.
2015-10-30 17:15 Reply Reply with quote Quote
Permalink 0 NEU
thank you so much. That save me a lot of time
2015-12-10 02:04 Reply Reply with quote Quote
Permalink 0 PC
I am trying to use this macro in order to delete unused formulas, because excel views blank formula cells as a zero value and will print extra pages. I was hoping when I deleted the unused formulas, when I printed it would only print the pages that had information. This is not the case and I really need help to find a solution. I have tried using this formula and it is not working and prints three extra pages that I do not need even with the extra formulas being deleted.

Sub selectonly()
'
' selectonly Macro
'

Range("A1").Sel ect
Range(Selection , Selection.End(x lToRight)).Sele ct
Range(Selection , Selection.End(x lDown)).Select
ExecuteExcel4Ma cro "PRINT(1,,,1,,, ,,,,,2,,,TRUE,, FALSE)"
End Sub
2016-01-08 16:11 Reply Reply with quote Quote
Permalink 0 Aju Thoas
Hi,

Thank you this was really helpful. However, there's an error that pops up when I run the codes it says "Object variable or with block variable not set" and it points to " the line DeleteRNG.Entir eRow.Delete". Could you please help me with debugging this.

Thanks.
2016-06-22 22:14 Reply Reply with quote Quote
Permalink 0 Marion
Hi everybody,

I am wondering what can we do to delete the following (According to the example shown in this page): Soe appears at several date (sept, October... etc). What I would like is to delete the line where Soe is but to keep the line with last date she appeared. In addition, some lines could be in double but I still want to keep it.

So for example, you have the lines:
- July 3 /Soe
- Sep 4 / Soe
- Sep 4 / Soe
- Oct 19/ Soe
- Nov 13 / Soe
- Nov 13 / Soe

and what I want to keep is:
- Nov 13 / Soe
- Nov 13 / Soe

My real case is: I have different EAN code and version 1, 2, 3 or 4 and I want to keep the line where the version is the higher.
e.g.: I have:
- EAN 1 / Version 1
- EAN 1 / Version 1
- EAN 1 / Version 2
- EAN 1 / Version 2
- EAN 2 / Version 2
- EAN 2 / Version 3
- EAN 2 / Version 3


and I want to keep:
- EAN 1 / Version 2
- EAN 1 / Version 2
- EAN 2 / Version 3
- EAN 2 / Version 3


I am searching since hours and I am completely blocked on this issue.

Many thanks in advance for your brain and help.

Best,

Marion
2016-11-28 13:13 Reply Reply with quote Quote
Permalink 0 Amar
Thanks for sharing. I am actually looking for a code that doesn't ask user for range but instead selects a specific column say column "A" and runs till the last row of that column. Can you please help..
2016-12-20 05:53 Reply Reply with quote Quote
Permalink 0 Bobby
Can someone help me...If 3rd column has value 0, then delete all values of corresponding column 1st.
In this case answer should be the last line only....

Check MenuName ID
3149 VNLA MILFLLE 2
3149 TURKEY PNN 0
3149 R. BEEF PNN 0
3149 MIX MOCHA 38
3150 M.G.R 1/2 0
3150 THE PEPPE L 0
3150 MIX SLD 0
3150 EGGPLANT 0
3150 STILL WATER 7
3151 MIX MOCHA 38
2017-02-17 17:18 Reply Reply with quote Quote

Add comment


Security code
Refresh