ASM Arman
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.
1. Delete 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 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.
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.
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.
- 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 the information about the employees working in the Sales department will be 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.
- 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.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
Similar Readings:
- How to Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)
- Delete Unfiltered Rows in Excel Using VBA (4 ways)
- How to Delete Every Other Row In Excel (4 Methods)
- Delete Infinite Rows in Excel (5 Easy Ways)
- How to Delete Rows in Excel That Go on Forever (4 Easy Ways)
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.
- 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.
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’.
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.
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!!!
