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


Download Practice Workbook


4 Ways to Delete Unfiltered Rows in Excel Using VBA

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 which 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 processed backward from there.

Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)


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 Male, 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: How to Delete Row Using VBA (14 Ways)


Similar Readings:


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: How to Delete Filtered Rows in Excel (5 Methods)


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.

Read More: How to Delete Hidden Rows in Excel (3 Methods) 


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


Conclusion

These are 4 different methods to Delete Unfiltered Rows in Excel 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

Mahbubur Rahman

Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo