Working with Autofilters in Excel VBA

Use the below VBA code, to show all the records in the sheet, without removing the already applied autofilter.

Sub ViewAllData()
  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If
End Sub

This code can be used, where we have applied multiple filters and to remove them at once, without removing the autofilter.


Use below code to view all the records in the protected sheet.

If the worksheet is protected, with no password, we can use below code to unprotect it, show all data and then turn the protection back on.

Sub ViewAllProtected()
With ActiveSheet
    .Unprotect
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True
End With
End Sub


Similarly we can use the below code to view all records in the password protected sheet

Sub ViewAllProtectedPwd()
Dim p As String
p = "password"
With ActiveSheet
    .Unprotect Password:=p
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:=p
End With
End Sub


Check and apply the Auto Filter

Below code will check if the auto filter is already applied in the sheet. If already applied then the code will exit, else it will apply the filter to the data.

Sub CheckAutoFilter()
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Sub


Removing the Autofilter

We can use the below code to remove excel AutoFilter using VBA code, if the autofilter already exists in the sheet.

Sub RemoveAutofilter()
  Worksheets("Sheet1").AutoFilterMode = False
End Sub

If there is no Autofilter already applied in the sheet, then the code will exit without executing anything.


Below code will help you to copy filtered rows

This code will copy the filtered rows to the sheet2 of the worksheet.

Sub CopyfilteredRows()

Dim r1 As Range, r2 As Range

With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set r2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With

If r2 Is Nothing Then
   MsgBox "No data available to copy"
Else
   Worksheets("Sheet2").Cells.Clear
   Set r1 = ActiveSheet.AutoFilter.Range
   r1.Offset(1, 0).Resize(r1.Rows.Count - 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
End If

   ActiveSheet.ShowAllData

End Sub

Counting visible rows in a filter

The below code will help us to get the number of total rows and out of which how many are visible.

Sub CountVisibleRows()

Dim r As Range

Set r = ActiveSheet.AutoFilter.Range

MsgBox r.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & r.Rows.Count - 1 & " Records"

End Sub

This code can be used where for example, we are counting the number of students in Group A. With the group as filtered using VBA code and the above message box code will provide us with the total and filtered row numbers.