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