How to Delete Filtered Rows in Excel (5 Methods)

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 them anymore. In this tutorial, I’ll show you 5 very easy ways of how to delete filtered rows in Excel.


Download Practice Workbook

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


5 Suitable Methods to Delete Filtered Rows in Excel

Let’s assume a scenario where we have information about employees of a company. We have the name 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.

Different Methods to Delete Filtered Rows in Excel


1. Delete Visible Filtered Rows

Step 1:

  • First, we will select the entire working area of our datasheet.

Delete Visible Filtered Rows

  • Select the Filter button from the Data tab, (under the ‘Sort and Filtergroup).

● Select the Filter button from the Data tab

  • You will notice a small downward arrow in the down-right corner of every cell of the header column. These small arrows will let you apply Filter on the respective column. You can click on an arrow to apply Filter on that corresponding column.

A Small Downward Arrow in the Down-right Corner of Every Cell

  • In this example, we will filter out only the rows containing the information about the employees working in the 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 quickly uncheck every type of department and then just select or check the Sales.
  • Click OK.

Delete Visible Filtered Rows in Excel

Step 2:

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

Select All the Filtered Rows in View

Step 3:

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

Delete Row From Pop-up

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

How to Delete Filtered Rows in Excel

  • This 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.
  • To see the rest of the data, simply click on the Filter button from the Data Tab.

Delete Filtered Rows in Excel


2. Remove 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 in a quicker way.

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.

Remove Visible Filtered Rows with VBA

  • Then Click Insert →Module.

Click Insert Module

Step 2:

  • After that enter the following code into the Module
Sub DeleteVisibleRows()
Dim Rng As Range
Set Rng = Selection
Rng.AutoFilter Field:=2, Criteria1:="Sales"
Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub
  • Then click the Run button to execute the code.

How to Delete Filtered Rows in Excel

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

All the Visible Filtered Rows Being Deleted


3. Delete 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 you have more complex filters, you would usually prefer to delete the rows that don’t qualify the criteria of applied filters, rather than those that do qualify.

In other words, you would prefer to delete the hidden rows after filtering.

Step 1:

  • First, we will select the entire working area of our datasheet.

Delete Hidden Filtered Rows Using the Inspect Document Feature

  • Select the Filter button from the Data tab, (under the ‘Sort and Filtergroup).

Select the Filter Button from the Data Tab

  • Select the filter arrow next to the Department header and uncheck all the boxes except for Sales.

How to Delete Filtered Rows in Excel

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

  • Click OK. You will now see only the rows of employees in the Sales department and have a blood group of B+.

Delete Visible Filtered Rows Using VBA

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 are sure you will never need to work with the hidden data again, then you can use Inspect Document feature of Excel to delete hidden rows.
  • Make a backup copy of your workbook (just in case).
  • Click on File →info Check for Issues.
  • Click on Inspect Document.

Inspect Document in Excel

  • This will open the ‘Document Inspector. Click on the Inspectbutton at the bottom of the window.

Click Inspect on Document Inspector

  • When you scroll down the list of options provided, you will see an option that says ‘Hidden Rows and Columns’. It will also tell you how many hidden rows and columns are present in your worksheet.
  • Click on the “Remove All” button. This will permanently delete all hidden rows in your sheet.
  • Click ‘Close’.

How to Delete Filtered Rows in Excel

  • Go back to your sheet and remove all filters by clicking on the Filter button from the Data tab again.

Delete Hidden Filtered Rows Using the Inspect Document Feature


4. Eliminate Hidden Filtered Rows with VBA

Once again, here’s a quicker 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 Alt + F11 keys simultaneously to open it.
  • Write the following code:
Sub KeepVisibleRows()
Dim myUnion As Range
Dim myRow As Range
Dim Rng As Range
Set Rng = Selection
Rng.AutoFilter Field:=2, Criteria1:="Sales"
Rng.AutoFilter Field:=3, Criteria1:="B+"
For Each myRow In Rng.Rows
If myRow.Hidden Then
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myRow)
Else
Set myUnion = myRow
End If
End If
Next
myUnion.Delete
ActiveSheet.AutoFilterMode = False
End Sub
  • Then click the Run button to execute the code.

Eliminate Hidden Filtered Rows with VBA

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

A Warning Pop-up BoxStep 2:

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

Delete Hidden Filtered Rows in Excel


5. Delete Hidden Rows by Creating a Temporary Column

If you don’t really want to go through the hassle of making backups or are worried about making permanent changes to other sheets in your worksheet, then here’s another way (more of a trick) to get rid of the hidden rows:

Step 1:

  • Create a temporary column anywhere in your dataset.
  • Type a ‘0’ on the first cell of this column and press the Return key.
  • Drag fill handle (on the bottom right corner) of this cell downward. This will copy the number ‘0’ to the rest of the column cells.

Delete Hidden Rows by Creating a Temporary Column

Step 2:

  • Remove the filters (by clicking on the Filter button from the Data tab again). This will bring back all your hidden rows too.

Remove the Filter

  • Now you need to reverse the filter. For this, select your work area and click on the Filter Select the arrow next to the header of the temporary column and uncheck the checkbox next to ‘0.

How to Delete Filtered Rows in Excel

  • Select all these rows, right-click and select “Delete”.

How to Delete Filtered Rows in Excel

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

Delete Hidden Filtered ROws

  • Once again remove the filters by clicking on the Filter and you can see the visible data remain intact.

Visible Filtered data Remain Intact


Things to Remember

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

Conclusion

In this article, we have learned to delete filtered rows in Excel. I hope from now on you would 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 do leave a comment below. Have a great day!!!

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo