Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

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

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! Click for 60-day free trial!
ad select special cells select entire rows columns if containing certain value



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


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

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 Select Same & Different Cells feature 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;
(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 listhave 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

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    kaushal · 1 months ago
    very well. Thank you!
  • To post as a guest, your comment is unpublished.
    Bobby · 8 months ago
    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.
    Amar · 10 months ago
    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.
    Marion · 10 months ago
    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.
    Aju Thoas · 1 years ago
    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.