How to Delete Filtered Rows in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Excel provides the user with a great functionality named Filter which helps us to only view the data we need while hiding all the non-relevant data. The filter will help you to focus more on specific data without the irrelevant information cluttering up the worksheet. While using Filter, you may need to get rid of this irrelevant information as you might not need it anymore. In this tutorial, I’ll show you 5 very easy ways to delete filtered rows in Excel.

Let’s assume a scenario where we have information about employees of a company. We have the names of the employees, the department they are working in, their blood group, and their joining date. Now, we will filter the data and delete both the visible and hidden rows using 5 different methods.

Table of Contents hide

1. Deleting Visible Filtered Rows

2. Removing Visible Filtered Rows with VBA

3. Deleting Hidden Filtered Rows Using the Inspect Document Feature

4. Eliminating Hidden Filtered Rows with VBA

5. Creating a Temporary Column to Delete Hidden Rows

Things to Remember

Conclusion

1. Deleting Visible Filtered Rows

Step 1:

First, we will select the whole data range of our worksheet.

Click on the Filter button under the ‘Sort and Filter’ section under the Data tab.

You will see a small downward arrow in the down-right corner of each header column. These small arrows will let you apply Filter on the respective column. Click on an arrow to apply Filter on that respective column.

For this example, we want to only filter out those rows containing information about the employees working in Sales. So, select the drop-down arrow in the down-right corner of the Department Header. A window will pop up that will let you filter the Department column according to your choice.
Uncheck all the boxes next to every type of Department except for the Sales.
You can just untick the Select All box to uncheck every type of department quickly and then select or check only the box next to the Sales.
Click OK.

Step 2:

Upon clicking OK, you will now see the information of those employees working in the Sales.

Step 3:

Select all the filtered rows in view and right-click with your mouse.
Click on Delete Row from the pop-up menu.

A warning pop-up box will appear and will ask if you want to delete the entire row.
Select OK.

It will delete the current rows containing the information about the employees working in the Sales department that we have filtered. But don’t worry!! It will not affect the other rows that are currently hidden.
You can simply click on the Filter button from the Data Tab, to see the rest of the data.

Similar Readings:

2. Removing Visible Filtered Rows with VBA

If you are familiar with VBA code or feel comfortable working with VBA. Then you can also use the VBA to get the above task done more quickly.

Step 1:

At first, select all the rows you need to filter (including the column headers).
Click Developer → Visual Basic, a new Microsoft Visual Basic for Applications window will be displayed.

Then Click Insert →Module.

Step 2:

After that enter the following code into the Module.

Sub Remove_Visible_Rows()
Dim R As Range
Set R = Selection
R.AutoFilter Field:=2, Criteria1:="Sales"
R.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

Then click the Run button to execute the code.

Following the execution of the program, all the rows containing information about the employees working in the Sales department will be deleted.

Similar Readings:

3. Deleting Hidden Filtered Rows Using the Inspect Document Feature

Let’s assume a scenario where we have a more complex filter to perform on our employee information. We might want to find out those employees working in the Sales department with a B+ blood group. In such a situation when we have to deal with more complex filters, we would usually prefer to remove the rows that fail to qualify the criteria of applied filters, rather than those rows that qualify the criteria of applied filters.

That means we would want to delete the hidden rows after filtering.

Step 1:

First, we will select the whole data range including the column header of our worksheet.

Click on the Filter option in the ‘Sort and Filter’ section under the Data tab.

Select the downward arrow (Filter Arrow) next to the Department header. Then uncheck all the boxes except the Sales.

Next, select the downward arrow next to the Blood Group header and uncheck all boxes except for B+.

Click on OK. Now, we will see only those rows of employees in the Sales department that have a blood group of B+.

Step 2:

Now we can delete the hidden rows. We can use three different methods to delete the hidden rows. One of them is the Inspect Document. If you do not have any usage of hidden data in the future, then you can use the Inspect Document feature of Excel to delete hidden rows.
Create a copy of your workbook.
Click on the File tab. Go to the Info option. Click on Check for Issues.
Select the Inspect Document option.

That will open the ‘Document Inspector’. Click on the ‘Inspect’ button.

Upon clicking the Inspect button, a new window with a list of options will appear. While you scroll down the list of options, you will come across an option titled ‘Hidden Rows and Columns’. It will have the number of you how many hidden rows and columns are there in your datasheet.
Select the “Remove All” option. That will permanently remove all the hidden rows.
Click on the ‘Close’ button.

We will go back to the worksheet and remove all applied filters by clicking on the Filter option.

Similar Readings:

4. Eliminating Hidden Filtered Rows with VBA

Here’s another quick way to get the above task done using the VBA script.

Step 1:

You can follow the previous method to open the Microsoft Visual Basic for Applications window or press the Alt + F11 keys simultaneously to open it.
Write the following code:

Sub Keep_Visible_Rows() 
Dim myU As Range 
Dim myR As Range 
Dim R As Range 
Set R = Selection 
R.AutoFilter Field:=2, Criteria1:="Sales" 
R.AutoFilter Field:=3, Criteria1:="B+" 
For Each myR In R.Rows 
   If myR.Hidden Then 
     If Not myU Is Nothing Then 
       Set myU = Union(myU, myR)
     Else Set myU = myR 
     End If 
   End If 
Next 
myU.Delete 
ActiveSheet.AutoFilterMode = False 
End Sub

Then click the Run button to execute the code.

A warning pop-up box will appear and will ask if you want to delete the entire row.
Select OK.

Step 2:

It will delete the hidden rows.
You can check if the hidden rows are removed by simply clicking on the Filter button from the Data tab again.

5. Creating a Temporary Column to Delete Hidden Rows

If you don’t want to take the hassle of making backups of the worksheet or are concerned about affecting or permanently damaging the other worksheets in your Excel file, then there’s another way to remove the hidden rows:

Step 1:

Create a column for temporary use anywhere on the worksheet. We have created one and named it Temporary.
Type ‘0’ on the first cell of the Temporary column and press ENTER.
Drag the fill handle of this cell downward. It will copy the number ‘0’ on the rest of the cells in the Temporary column. Alternatively, you can also double-click on the fill handle to populate all the cells in the range with the number ‘0’.

Step 2:

Click on the Filter option to remove the filters. This will also bring all your hidden rows back again.

Now we will reverse the filter we applied before. To do this, select your entire data range including the column header, and click on the Filter. Click on the downward arrow on the down-right corner of the header of the temporary column and deselect all the checkboxes next to the value ‘0’.

Now, select all these rows currently visible, right-click on any cell, and click on the “Delete Row” option.

A warning pop-up box will appear and will ask if you want to delete the entire row.
Select OK.

Once again click on the Filter option to remove the filters and you can see the visible data remain intact.

Similar Readings:

Things to Remember

If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
To open the VBA editor Press ALT + F11.
You can press ALT + F8 to bring up the Macro window.

Related Content: How to Delete Rows in Excel with Specific Text

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Delete Filtered Rows.xlsm

Conclusion

In this article, we have learned to delete filtered rows in Excel. I hope from now on you will find it very easy to delete both visible and hidden filtered rows in Excel. If you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!

Related Content: How to Remove Highlighted Rows in Excel

How to Delete Filtered Rows in Excel?

1. Deleting Visible Filtered Rows

2. Removing Visible Filtered Rows with VBA

3. Deleting Hidden Filtered Rows Using the Inspect Document Feature

4. Eliminating Hidden Filtered Rows with VBA

5. Creating a Temporary Column to Delete Hidden Rows

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply