Menu
How to remove rows based on cell value in Excel?
Apr 12, 2018 - Hi Jessica. As variant you may Data->Filter blank rows and delete them at once. Hided by filter rows won't be deleted. How to Delete only Filtered Rows without the Hidden Rows in Excel 2010 In this article we will learn how to delete only filtered rows without the hidden rows in excel 2010. While working with Excel, there could be situations where we need to delete certain rows after hiding some particular rows.
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 or two cell values
Remove rows based on multiple cell values
Quickly select entire row/column by certain cell value, and then delete selected row/column at ease!
Kutools for Excel’s Select SpecifiC Cells utility provides Excel users an easy choice to select the entire row or entire column if cell values match certain value in Excel. Easier and more distinct for working!Full Feature Free Trial 60-day!
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel - The Best Office Productivity Tool Will Solve Most of Your Excel Problems
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than300 powerful features; Works with Office2007-2019 and 365; Supports all languages; Easy deploying in company; Full features60-day free trial.
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
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.
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
![How To Delete Multiple Rows In Excel How To Delete Multiple Rows In Excel](/uploads/1/2/5/6/125688577/725285307.png)
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:
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.
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:
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 60-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.
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.
(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 Okbutton 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.
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
In this Video, Kutools and Kutools Plus tabs are added by Kutools for Excel. If need it, please click for 60-day free trial without limitation!
Kutools for Excel - The Best Office Productivity Tool Increase Your Productivity by80%
- 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 than300 powerful features. Supports Office/Excel2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features60-day free trial.
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 by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
![How To Delete Multiple Rows In Excel How To Delete Multiple Rows In Excel](http://orinoko2468.club/wp-content/uploads/2018/07/excel-how-to-delete-empty-rows-excel-macro-delete-row-how-to-delete-all-blank-rows-in-excel-how-to-remove-blank-excel-delete-blank-rows-multiple-columns.jpg)
Loading comment... The comment will be refreshed after 00:00.
- very well. Thank you!
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.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..
- To post as a guest, your comment is unpublished.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
[i][b]My real case is:[/b][/i] 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 - To post as a guest, your comment is unpublished.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.EntireRow.Delete'. Could you please help me with debugging this.
Thanks. - To post as a guest, your comment is unpublished.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').Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ExecuteExcel4Macro 'PRINT(1,1,2,TRUE,FALSE)'
End Sub - To post as a guest, your comment is unpublished.thank you so much. That save me a lot of time
- To post as a guest, your comment is unpublished.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.
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.Great it work thank you so much
- To post as a guest, your comment is unpublished.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! :-)
- To post as a guest, your comment is unpublished.Just what I needed, thanks! :D
- To post as a guest, your comment is unpublished.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.
- To post as a guest, your comment is unpublished.What a great. thank so all so much. :)
- To post as a guest, your comment is unpublished.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?
- To post as a guest, your comment is unpublished.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?
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.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.
- To post as a guest, your comment is unpublished.I'd like to delete all rows where column1 = 'Apple' AND column3 = 'green'. please?
- 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.
- To post as a guest, your comment is unpublished.thank.. it helps me a lot...
- To post as a guest, your comment is unpublished.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?
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.Hi,
What is the easiest way to delete rows that do NOT contain 'Apple' please?
Thanks - To post as a guest, your comment is unpublished.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. - To post as a guest, your comment is unpublished.Definitely awsome! Appreciate for sharing!!!
- To post as a guest, your comment is unpublished.I just wanted to say thank you. This worked like a charm.
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.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. - To post as a guest, your comment is unpublished.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)
- To post as a guest, your comment is unpublished.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 - To post as a guest, your comment is unpublished.Bobby, try this, it should get what you want:
If (cell.Value) = 'Apple' OR (cell.Value) = 'Monday'- Is there anyway to have the value recognize a value whether is > or = to a certain value?
- To post as a guest, your comment is unpublished.Any way to modify the If (cell.Value) = 'Apple' to include multiple values?
- To post as a guest, your comment is unpublished.Fantastic! That worked a treat, I altered it slightly to suit what I needed but pure genious. Oh VBA Version.