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 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 whole data range of our worksheet.

Delete Visible Filtered Rows

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

● Select the Filter button from 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.

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

  • For this example, we want to only filter out those 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 uncheck every type of department quickly and then select or check only the box next to 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.
  • Click on 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

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

Delete Filtered Rows in Excel

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)


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

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

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

Read More: Formula to Remove Blank Rows in Excel (5 Examples)


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

Delete Hidden Filtered Rows Using the Inspect Document Feature

  • Click on the Filter option in the ‘Sort and Filtersection under the Data tab.

Select the Filter Button from the Data Tab

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

How to Delete Filtered Rows in Excel

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

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

Inspect Document in Excel

  • That will open the ‘Document Inspector. Click on the Inspectbutton.

Click Inspect on Document Inspector

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

How to Delete Filtered Rows in Excel

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

Delete Hidden Filtered Rows Using the Inspect Document Feature

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)


Similar Readings:


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

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:

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

Delete Hidden Filtered Rows in Excel

Read More: How to Delete Hidden Rows in Excel VBA (A Detailed Analysis)


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

Delete Hidden Rows by Creating a Temporary Column

Step 2:

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

Remove the Filter

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

How to Delete Filtered Rows in Excel

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

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 click on the Filter option to remove the filters and you can see the visible data remain intact.

Visible Filtered data Remain Intact

Read More: How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)


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


Related Articles

ASM Arman

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