Remove rows based on cell value in Excel – Easy tutorial

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

• By using Filter feature
• By using Kutools AI Aide
• By using Find and Replace feature
• By using VBA code

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.

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.

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.

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.
   

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.

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: