Note: The other languages of the website are Google-translated. Back to English
English English

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 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 cell 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 containing the certain value are selected.

4. Go ahead to 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 OK button. Now you will see all the cells containing the certain value are removed. See screenshots below:

And then entire rows have been deleted based on the certain value already.


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()
'Updateby20211217
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Dim xTitleId As String
Dim xArr
Dim xF As Integer
Dim xWSh As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, rng.Address, Type:=8)
If InputRng Is Nothing Then Exit Sub
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
Set xWSh = InputRng.Worksheet
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)
            Set DeleteRng = DeleteRng.EntireRow
        End If
    End If
Next
xArr = Split(DeleteRng.AddressLocal, ",")
DeleteRng.Select
DeleteRng.Delete
For xF = UBound(xArr) To 0 Step -1
    Set DeleteRng = xWSh.Range(xArr(xF))
    DeleteRng.Delete
Next
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.


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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the range that you will remove rows based on the certain value, and click 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 right 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 the selected rows, and click the Delete from the right-clicking menu. See screenshot below:

Note: This Select Specific Cells feature supports to delete rows by one 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:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Remove 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 Select Same & Different Cells feature to solve it quickly with ease.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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

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 containing values among the specified list have been selected.

4. Click Home > Delete > Delete Sheet Rows to delete all selected rows.


Demo: remove rows based on one or multiple cell values in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Fantastic! That worked a treat, I altered it slightly to suit what I needed but pure genious. Oh VBA Version.
This comment was minimized by the moderator on the site
Any way to modify the If (cell.Value) = "Apple" to include multiple values?
This comment was minimized by the moderator on the site
Bobby, try this, it should get what you want: If (cell.Value) = "Apple" OR (cell.Value) = "Monday"
This comment was minimized by the moderator on the site
Is there anyway to have the value recognize a value whether is > or = to a certain value?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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)
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
I just wanted to say thank you. This worked like a charm.
This comment was minimized by the moderator on the site
Definitely awsome! Appreciate for sharing!!!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi, What is the easiest way to delete rows that do NOT contain "Apple" please? Thanks
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
thank.. it helps me a lot...
This comment was minimized by the moderator on the site
I'd like to delete all rows where column1 = "Apple" AND column3 = "green". please?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
What a great. thank so all so much. :)
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations