Skip to main content

Remove rows based on cell value in Excel – Easy tutorial

Author: Xiaoyang Last Modified: 2024-04-09

Whether you're dealing with a small spreadsheet or a large dataset, knowing how to remove rows based on cell values can save you time and effort. This process can be performed using several Excel features, including the Filter feature, the Find and Replace tool, the Sort feature, and even using advanced techniques. By the end of this guide, you will be equipped with the knowledge to efficiently remove unwanted rows from your Excel sheets, ensuring your data is clean and relevant to your analysis needs.

For example, if I want to remove all rows where the name column contains Jener, how would I proceed?

Remove entire rows based on cell value

Remove rows in the selection based on cell value with Sort feature


Remove entire rows based on cell value in Excel

Excel offers several ways to remove rows based on specific cell values. This section covers methods ranging from simple Excel features like filtering, find and replace and Kutools to more advanced techniques involving VBA. Whether you're a beginner or an advanced user, you'll find a method that suits your skill level and needs.

📝 Note: The following methods to remove rows will remove all data within that row, including content in other cells of the row. To prevent data loss, consider duplicating the dataset onto another worksheet before deletion.

Remove entire rows based on cell value with Filter feature

Normally, you can apply the Filter feature to display the rows that meet specific criteria, and then delete the row at once.

Step 1:Apply the Filter feature to filter the unwanted rows

  1. Select the data range that you want to delete rows. And then, click Data > Filter, see screenshot:
  2. Now, dropdown arrows will appear in the header of each column. Click on the dropdown arrow in the column containing the values based on which you want to remove rows. For example, if you’re looking to remove rows with the name Jener, you’d click on the arrow in the Name column.
  3. In the dropdown menu, uncheck Select All to clear all selections, and check only the box next to Jener or manually enter Jener in the search box to quickly find it.
  4. And then, click OK button to apply the filter. Only the rows that contain Jener will be displayed. See screenshot:

Step 2: Delete the filtered rows

Select the visible rows, then, right-click on one of the selected rows and choose Delete Row from the context menu. This will remove all selected rows.

Step 3: Clear the filter

Then, click on the Data > Filter again. This will remove the filter and you will see all the records except the deleted ones.

💡 Tips:
  • Similar to how I apply the filter method to eliminate rows containing the text Jener, you can also utilize it to remove rows based on numerical or date conditions as following screenshots shown:
    Number filter conditions:


    Date filter conditions:
  • You can also use the Filter feature to filter and delete all rows that contain certain background color. See screenshot:
  • You can use the filter feature to filter based on multiple criteria to filter out the content you don't need and then delete it. For example, suppose you want to delete all rows where the salesman is Jener and the total sales are greater than $7000. You should filter rows by name Jener firstly, then filter the sales which are greater than 7000.
 

Remove entire rows based on cell value with Kutools AI Aide

Kutools for Excel is a comprehensive add-in that includes more than 300 advanced features, designed to simplify various kinds of complex tasks in Excel. One of its features, Kutools AI Aide, helps automate tasks such as filtering and deleting rows based on specific criteria.

📝 Note: To use this Kutools AI Aide of Kutools for Excel, please download and install Kutools for Excel first.

After installing Kutools for Excel, please click Kutools AI > AI Aide to open the Kutools AI Aide pane:

  1. Select the data range, then type your requirement into the chat box, and click Send button or press Enter key to send the question;
    “Remove the rows if the Name column contains the name Jener in the selection”
  2. After analyzing, click Execute button to run. Kutools AI Aide will process your request using AI and remove the specified rows directly in Excel.
💡 Tips:
  • To delete rows based on multiple criteria, simply adjust the requirements accordingly. For example, use commands like "Remove rows where the Name column includes Jener or Kevin in the selection" or "Remove rows where the Name column contains Jener and total sales exceed 7000 in the selection".
  • This method does not support the undo function. However, if you wish to restore the original data, you can click Unsatisfied to revert the changes.
 

Remove entire rows based on cell value with Find and Replace feature

Removing entire rows based on a specific cell value using Excel's Find and Replace feature is a straightforward method that can be highly effective for cleaning up your data. Please do as follows:

Step 1: Apply the Find and Replace feature to select the specific value

  1. Select the column data where you will remove rows based on certain cell value, and then open the Find and Replace dialog box by 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 Jener) into the Find what box, and click the Find All button. See screenshot:
  3. Select all searching results at the bottom of Find and Replace dialog box, and close this dialog box. (You can select one of searching result, and then Ctrl + A keys to select all found results.) And all the cells containing the certain value are selected. Then, close this dialog box. See screenshot:

Step 2: Delete the Rows based on the selected value

  1. With the cells still selected, right-click on one of the selected cells and choose Delete from the context menu.
  2. Select Entire row in the Delete dialog and click OK to remove all the rows containing the specified value.
 

Remove entire rows based on cell value with VBA code

Removing rows based on cell value using VBA (Visual Basic for Applications) in Excel allows for automation of the task, making it efficient, especially for large datasets.

Step 1: Open the VBA module editor and copy the code

  1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
  2. In the opened window, click Insert > Module to create a new blank module.
  3. Then, copy and paste the below code into the blank module.
    Sub DeleteRowsBasedOnCellValue()
    'Updateby Extendoffice
        Dim ws As Worksheet
        Set ws = ActiveSheet
        Dim columnRange As Range
        On Error Resume Next
        Set columnRange = Application.InputBox("Select the column range to check:", "Kutools for Excel", Type:=8)
        On Error GoTo 0
        If columnRange Is Nothing Then Exit Sub
        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, columnRange.Column).End(xlUp).Row
        Dim criteria As String
        criteria = Application.InputBox("Enter the value to delete rows for:", "Kutools for Excel", Type:=2)
        If criteria = "" Then Exit Sub
        Dim i As Long
        For i = lastRow To 1 Step -1
            If ws.Cells(i, columnRange.Column).Value = criteria Then
                ws.Rows(i).Delete
            End If
        Next i
    End Sub
    

Step 2: Execute the code

  1. Then press F5 key to run the code. In the popping up dialog box, please select the column where you will remove rows based on the certain value, and click the OK button.
  2. In following dialog box, please type the certain value you will remove rows based on, and clicks the OK button. See screenshot:

Result:

And then you will see entire rows have been deleted based on the specified value already.

📝 Note: This VBA code method does not support Undo, so ensure the operation performs as expected before using it. Additionally, maintaining a backup of your data is advisable to safeguard against unintended changes or loss.

Remove rows in the selection based on cell value with Sort feature

All of the above methods remove the entire row, which can be a limitation. For instance, using these methods would eliminate all data to the right of the dataset. Suppose you wish to delete only certain records within the dataset while preserving the rest of the data. In that case, an alternative approach is needed.

Step 1: Create a helper column for the data

Create a new column which will help us to track the order. You need to insert a column next to your data and then fill it with a serial number like 1, 2, 3… see screenshot:

Step 2: Sort the data based on the specific column

  1. Select the data column that you want to delete rows based on. Then, click Data > Sort A to Z or Z to A, and a Sorting Warning dialog box is popped out, select Expand the selection, and then, click Sort button. See screenshot:
  2. Now, rows with the same values will be grouped together, making it easy to identify and select them. See screenshot:

Step 3: Delete the sorted rows

  1. Select the group of records that you want to delete, and then right-click, then choose Delete from the context menu. In the Delete dialog box, select Shift cells up option. And then, click OK Button. See screenshot:
  2. Only the records where the name is Jener are deleted without removing the entire row. Thus, any data to the right or left of your dataset will remain unaffected.

Step 4: Apply the Sort feature to restore the original order of the data

Click the header of the help column, then go to Data on the ribbon and select Sort A to Z to organize the data according to the sequence in the helper column. This will restore the data within the selected range to its original order as required.

Step 5: Delete the helper column

Finally, delete the helper column as necessary to clean up your worksheet.


In this article, we've explored several methods to remove rows in Excel based on cell values. You can choose the one you like. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


Related Articles:

  • Easily remove blank cells in Excel – Full tutorial
  • Removing blank cells in Excel is a common task that can help streamline your data, making it easier to analyze, understand, and present. Blank cells can disrupt your data analysis, cause errors in formulas, and make your datasets look incomplete or unprofessional. In this tutorial, we'll explore several methods to efficiently remove or manage blank cells in Excel, such as Go To Special feature, formulas, Filter function. Each method serves different needs and scenarios, so you can choose the one that best fits your requirements.
  • Delete rows based on background color
  • How could you delete entire rows based on background color? In this example, I need to delete all rows which cells filled with blue background color as following screenshot shown. With this article, you will get some codes to achieve this task in Excel.
  • Delete every other row
  • If you want to quickly delete every other row or column in Excel, the main point is how can you quickly select every other row or column first and then apply the delete operation to them. And this article will show you some tricky things about how to select every other row or column first and then delete them quickly.
Comments (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
In given range, if any cell contains defined text the entire row gets deleted.

Pls share code
This comment was minimized by the moderator on the site
Hi Yogesh,
On this webpage, the second method is to delete rows by cell values with VBA code. You can go to there and copy the code directly!
https://www.extendoffice.com/documents/excel/815-excel-remove-rows-based-on-cell-value.html#vba
This comment was minimized by the moderator on the site
There are two issues with the code posted as of this date. 1) the variable xTitleId is not declared, which causes a compilation error if Option Explicit is in use. 2) With Excel 2016, it appears that a range is limited to 129 areas. Each discontiguous matching cell found in the loop creates another area in the DeleteRng variable. Unfortunately, the Union method does not raise an error if that number of areas is exceeded; it just ignores the remaining cells. When the loop is finished, DeleteRng will have no more than 129 areas.
This comment was minimized by the moderator on the site
Hi TomTheToolman,
Thank you so much for your feedback. I've already updated the new code in the article. If you have any other questions, please don't hesitate to let me know.
Amanda
This comment was minimized by the moderator on the site
very well. Thank you!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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..
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations