How to Delete Unfiltered Rows in Excel Using VBA (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, when while working with a dataset we want to filter data and only want the visible cells and delete unfiltered rows in Excel. In this article, we will see how to delete unfiltered rows in Excel Using VBA. For your better understanding, we will use a sample dataset containing Names, Gender, State, Age, and Marital Status.

Delete Unfiltered Rows VBA


How to Delete Unfiltered Rows in Excel Using VBA: 4 Ways

We will see the use of VBA to delete unfiltered or hidden rows in Excel from a defined range or the entire worksheet.

Method 1: Delete Unfiltered Rows Using Excel VBA from Used Range

We will see the use of VBA in deleting unfiltered rows from our used range in this method. Here, our data is filtered with Male, we don’t want data for females that are unfiltered and hidden.

Delete Unfiltered Rows VBA used range

Steps:

  • First, right-click on the sheet and go to View Code.

  • After that, copy and paste the VBA code below.

VBA code:

Option Explicit
Sub DeleteUnfilteredRows()
Dim MYsht As Worksheet
Dim LastRow
Set MYsht = ActiveSheet
LastRow = MYsht.UsedRange.Rows(MYsht.UsedRange.Rows.Count).Row
For i = LastRow To 1 Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
End Sub

  • After that, press the F5 or play button to run the code.

Delete Unfiltered Rows VBA range

As you can see, all the Unfiltered rows are deleted from the data range.
Here, through this code, we are telling Excel to delete all the hidden and unfiltered rows. While using this code we have to command Excel to count rows from the last rows in the range and it is processed backward from there.

Read More: How to Delete Rows in a Range with VBA in Excel


Method 2: Delete Unfiltered Rows Using VBA INTERSECT Method

Now, what should we do, if we want to delete all rows from a defined range? Let’s see. We have filtered data for Males, but we want to play safe and delete all unwanted rows from range A1:Z100.

Delete Unfiltered Rows VBA used range

Steps:

  • First, right-click on the sheet and go to View Code.

  • After that, copy and paste the VBA code below.

VBA code:

Option Explicit
Sub RemoveUnfilteredRows()
Dim oRow As Range, rng As Range
Dim DefinedRows As Range
With Sheets("Defined Range")
Set DefinedRows = Intersect(.Range("A1:Z100").EntireRow, .UsedRange)
If DefinedRows Is Nothing Then Exit Sub
End With
For Each oRow In DefinedRows.Columns(1).Cells
If oRow.EntireRow.Hidden Then
If rng Is Nothing Then
Set rng = oRow
Else
Set rng = Union(rng, oRow)
End If
End If
Next
If Not rng Is Nothing Then rng.EntireRow.Delete
End Sub

  • After that, press the F5 or play button to run the code.

Delete Unfiltered Rows VBA in excel

Here, we declared our range from A1:Z100 and declared our sheet name using  With Sheets(“Defined Range”) command. As a result, Excel has deleted all the unfiltered rows in the given range.

Read More: Excel VBA: Delete Row on Another Sheet


Method 3: VBA Application Object to Delete Hidden Rows

Unfiltered or hidden rows are somewhat the same. When we filter data, the rest of the data gets hidden as you can see from the image below. Here, we filtered with Married for Marital Status.

Steps:

  • First, right-click on the sheet and go to View Code.

Delete hidden Rows VBA

  • After that, copy and paste the VBA code below.

VBA code:

Option Explicit
Sub DeleteHiddenUnfilteredRows()
Dim Rows As Long
Application.Calculation = xlCalculationManual
For Rows = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Cells(Rows, 1).EntireRow.Hidden = True Then Cells(Rows, 1).EntireRow.Delete
Next Rows
Application.Calculation = xlCalculationAutomatic
End Sub

  • After that, press the F5 or play button to run the code.

As you can see, all the hidden unfiltered rows are deleted. This code easily deletes all unwanted hidden rows in a used range.

Read More: Excel VBA to Delete Table Row


Method 4: Delete Hidden Rows in Excel from Active Worksheet

There is another method to delete hidden or unfiltered rows in Excel VBA. Here we not only delete the rows but will use a message box to show the number of rows deleted. Here we filtered with Married for Marital Status.

Steps:

  • First, right-click on the sheet and go to View Code.

Delete Unfiltered and hidden Rows VBA Excel

  • After that, copy and paste the VBA code below.

VBA Code:

Option Explicit
Sub UnfileredRowRemoveMsG()
Dim MyRow As Range
Dim MyRg As Range
Dim MyRows As Range
On Error Resume Next
Set MyRows = Intersect(ActiveSheet.Range("A1:Z500").EntireRow, ActiveSheet.UsedRange)
If MyRows Is Nothing Then Exit Sub
For Each MyRow In MyRows.Columns(1).Cells
If MyRow.EntireRow.Hidden Then
If MyRg Is Nothing Then
Set MyRg = MyRow
Else
Set MyRg = Union(MyRg, MyRow)
End If
End If
Next
If Not MyRg Is Nothing Then
MsgBox MyRg.Count & " hidden rows have been deleted", , "Unfiltered Row Delete"
MyRg.EntireRow.Delete
Else
MsgBox "No hidden rows found", , "Unfiltered Row Delete"
End If
End Sub

  • After that, press the F5 or play button to run the code.

As you can see, it has identified three hidden and unfiltered rows, and clicking OK will delete this.


Through this code, we are not only commanding Excel to delete unfiltered rows but also commanding it to return the calculation result in a message box.


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.

Delete Unfiltered Rows VBA


Download Practice Workbook


Conclusion

These are 4 different methods to Delete Unfiltered Rows in Excel using VBA. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo