How To Delete Filtered Rows In Excel

Excel provides a wide range of functionality, a lot of which most users are non even aware of. One of its well-nigh fascinating features is the versatility of its Filters.

Excel filters provide a slap-up way for you to view only the data that you need while keeping not-relevant rows hidden.

This way, you go to focus on specific data, without the clutter. While operating 1 of these filters, y'all might feel the demand to delete some of these 'non-relevant' rows, mayhap because you don't need them anymore.

For instance, in the dataset below, we have data most a visitor's employees. Equally y'all can encounter, some of these employees are retired, some are still in service, while few of them are on probation.

Y'all might not need the information nigh retired employees anymore. So, you might want to delete rows containing information about retired employees.

The problem is, you don't want to manually search every row to find the ones with Employment Status= "Retired". That beats the whole purpose of using computerized spreadsheets, doesn't information technology?

The all-time solution is to utilize filters to assist you in the process. In this article, nosotros volition evidence you how to delete filtered rows in Excel.

We will show you lot how to delete both the visible rows after you apply a filter, as well as the invisible rows.

If y'all like to code and would like to speed up your Excel processes using VBA code, nosotros accept a few snippets of code that you can apply as well.

Click here to download the example file and follow along

Deleting Filtered Rows that are Visible

For the higher up problem, you tin can filter the rows that y'all don't need and then delete these rows. Here'south how:

Select the entire working area of your dataset.
From the Data tab, select the Filter button (under the 'Sort and Filter' group).
You will notice small arrows on every prison cell of the header row. These are meant to assistance you filter your cells. You lot can click on any arrow to choose a filter for the corresponding column.
In this case, we want to filter out just the rows that contain the Employment status "Retired". So, select the arrow next to the Employment Status header and uncheck the boxes next to all the statuses, except "Retired". Yous can but uncheck "Select All" to chop-chop uncheck everything and so simply select "Retired".
Click OK. Y'all will now see only the rows with Employment Condition "Retired".
Select all the rows in a view, right-click and select "Delete Row" from the popup carte. This will delete all the visible rows only. Don't worry, the rest of your data is safe.
To see the residue of the data, only click on the Filter button from the Information tab again.

You should now see all your data, without the rows containing details about Retired employees.

Deleting Filtered Rows that are Visible using VBA

If you lot are comfortable with the idea of writing (or copy-pasting) some VBA code, hither's a quicker way to get the above chore done. Nosotros've put together a short script that you tin can just re-create, customize, and employ:

Sub DeleteVisibleRows() Dim Rng As Range Set Rng = Selection Rng.AutoFilter Field:=five, Criteria1:="Retired" Rng.Offset(ane, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = False End Sub

This code takes a choice of rows and applies the 'AutoFilter' to it according to your criteria (line 4). This makes sure that merely rows that authorize the given criteria get displayed.
```
Rng.AutoFilter Field:=5, Criteria1:="Retired"
```
After this, the code checks for only those rows that are visible (barring the column headers) and deletes them (line 5).
```
Rng.Offset(ane, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
```
Finally, it removes the filters by setting the sheet'south AutoFilterMode to Fake (line vi).
```
ActiveSheet.AutoFilterMode = Imitation
```

Follow these steps to utilise the above code:

Select all the rows you lot demand to filter (including the column headers)
From the Developer Menu Ribbon, select Visual Bones.
Once your VBA window opens, Click Insert->Module and paste the to a higher place code in the Module window.
Run this script by navigating to Developer->Macros-> DeleteVisibleRows or clicking on the green play button from the toolbar on top.

Note: Y'all can change line 4 to fit your own filter criteria. Replace the number '5' with the number of the column you want to use the filter to. That ways if you desire to apply the filter to column A, you lot need to change it to "1". Supervene upon the criterion "Retired" with the Criterion you lot want the filter to satisfy (for deletion). Likewise note that if you have more one criteria, yous can repeat this line for each criterion.

Deleting Filtered Rows that are Hidden

Allow us now come up to a scenario where you have a more than circuitous filter.

Say, yous only want details of Sales employees who are even so in service or on probation. When y'all have more circuitous filters, 1 would usually prefer to delete the rows that don't qualify, rather than those that do authorize.

In other words, one would prefer to delete the rows that are hidden after filtering.

Let us use the same dataset that we had used earlier and this time, apply two filters – one to remove rows with Employment Status= "Retired" and some other to remove any row that does non contain Department= "Sales"

Here's the filtering procedure first:

Select the entire working expanse of your database
From the Information tab, select the Filter button (under the 'Sort and Filter' group).
Select the filter arrow side by side to the Employment Status header and uncheck the box next to "Retired".
Adjacent, select the filter pointer side by side to the Department header and uncheck all boxes except "Sales".
Click OK. Y'all volition now see simply the rows with Employment Condition either "In service" or "on probation" and Department="Sales".

Now information technology is time to delete the hidden rows. In that location are three ways to do this.

Deleting Hidden Rows using the Inspect Certificate feature

If yous are sure you volition never need to work with the subconscious information again, and then this method tin can piece of work for yous:

Make a backup re-create of your workbook (just in case).
Click on File->Check for Problems.
Click on Inspect Document.
This will open the 'Certificate Inspector'. Click on the 'Audit' button at the bottom of the window.
When y'all curl down the list of options provided, you lot volition come across an option that says 'Subconscious Rows and Columns'. Information technology will also tell yous how many hidden rows and columns are present in your worksheet.
Click on the "Remove All" button. This will permanently delete all subconscious rows in your sheet.
Click 'Close'.
Go back to your canvas and remove all filters by clicking on the Filter button from the Data tab again.

Yous will discover that even after all filters are removed, y'all are left with only your required rows. All other rows accept been deleted.

Deleting Subconscious Rows by Creating a Temporary Cavalcade

If you don't really desire to carp with 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 go rid of your hidden rows:

Create a temporary cavalcade anywhere in your dataset.
Type a '0' on the beginning cell of this cavalcade and press the Render key.
Double click on the fill handle (on the bottom correct corner) of this cell. This volition copy the number '0' to the rest of the cavalcade cells.
Remove the filters (past clicking on the Filter button from the Information tab over again). This will bring back all your hidden rows also.
At present y'all need to reverse the filter. For this, select your work expanse and click on the Filter button. Select the arrow next to the header of the temporary column and uncheck the checkbox side by side to '0'.
Select all these rows, right-click and select "Delete".
Once again remove the filters past clicking on the Filter push.

You should now exist left with just your required rows.

Deleting Filtered Rows that are Hidden using VBA

Once once again, here's a quicker way to get the to a higher place job done. You will discover below a brusk script that y'all tin can copy, customize, and employ:

Sub KeepVisibleRows() Dim myUnion As Range Dim myRow As Range Dim Rng Equally Range Set Rng = Choice Rng.AutoFilter Field:=iv, Criteria1:="Sales" Rng.AutoFilter Field:=5, Criteria1:="<>Retired" ForEach myRow In Rng.Rows If myRow.Subconscious And then If Not myUnion IsNothingThen Fix myUnion = Union(myUnion, myRow) Else Gear up myUnion = myRow EndIf EndIf Adjacent myUnion.Delete ActiveSheet.AutoFilterMode = False End Sub

Follow these steps to apply the above lawmaking:

Select all the rows you lot need to filter (including the column headers)
From the Developer Card Ribbon, select Visual Basic.
Once your VBA window opens, Click Insert->Module and paste the in a higher place lawmaking in the Module window.
Run this script past navigating to Developer->Macros-> KeepVisibleRows or clicking on the green play button from the toolbar on top.

Here'southward an explanation of the lawmaking:

This code takes a selection of rows and applies the 'AutoFilter' to it co-ordinate to your criteria (lines 6 and 7).
```
Rng.AutoFilter Field:=iv, Criteria1:="Sales"  Rng.AutoFilter Field:=five, Criteria1:="<>Retired"
```
In line #6 we have specified that nosotros want but the rows with Department equal to "Sales" (column four) to be displayed.
```
Rng.AutoFilter Field:=4, Criteria1:="Sales"
```
Similarly, in line seven we have specified that we want only the rows with Employment Status not equal to "Retired" (column 5) to be displayed (The symbol "<>" means "non equal to").
```
Rng.AutoFilter Field:=5, Criteria1:="<>Retired"
```
These two lines ensure that only the rows that satisfy both the criteria become displayed, while the others remain hidden.
After this, the lawmaking loops through each row and checks if whatever of them are hidden (line 9).
```
If myRow.Hidden Then
```
If so, they are grouped together into a temporary range named "myUnion". In one case all the rows have been looped through, all the hidden rows volition be added to myUnion.
```
Fix myUnion = Union(myUnion, myRow)
```
Afterwards the looping ends, all the rows that are grouped in myUnion will go deleted (line 17). This means all the subconscious rows will get deleted in 1 go.
```
myUnion.Delete
```
Finally, all filters will get removed by setting the sheet's AutoFilterMode to Imitation (line xviii).
```
ActiveSheet.AutoFilterMode = Fake
```

Note: As mentioned before, you tin can alter lines 6 and vii to fit your own filter criteria. If you lot have more than one criteria, you can repeat these lines for each criterion.

Conclusion

In this tutorial, we showed you two means to delete the visible rows after applying filters to them and 3 ways to delete the hidden rows after applying filters.

We have also provided short VBA scripts to help yous get the work washed faster if you feel confident using scripts. We hope you found this tutorial helpful.

Do permit u.s.a. know in the comments if you face any issues while following whatever of the steps.

Other Excel tutorials you may detect useful:

How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
How to Unhide All Rows in Excel with VBA
How to Gear up a Row to Print on Every Page in Excel
How to Delete a Sheet in Excel Using VBA
How to Paste in a Filtered Column Skipping the Hidden Cells
How to Hide Columns Based On Cell Value in Excel
How to Add together a Total Row in Excel Tabular array
How to Select Rows with Specific Text in Excel
How to Remove Duplicate Rows based on 1 Column in Excel?
How to Move Row to Some other Canvas Based On Cell Value in Excel?