Excel Filters AutoFilter Macros

Sales KPIs Reports

Examples of AutoFilter macros, for use with worksheet AutoFilters
(only one allowed per worksheet).

Show All Records

The following Excel AutoFilter VBA code shows all records, if a filter
has been applied.

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

Show All Records on Protected Sheet

The following macros are designed for sheets that are protected. There are two versions of the macro:

Protected Sheet with No Password

If the worksheet is protected, with no password, use this
code to unprotect it, show all, then turn the protection back on.

Sub ShowAllProtected()

With ActiveSheet
    .Unprotect
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True
End With

End Sub

Protected Sheet with a Password

If the worksheet is protected, with a password, use this code to
unprotect it, show all, then turn the protection back on.

Sub ShowAllProtectedPwd()
Dim strPwd As String
strPwd = "yourpassword"

With ActiveSheet
    .Unprotect Password:=strPwd
    .ShowAllData
    .Protect _
        Contents:=True, _
        AllowFiltering:=True, _
        UserInterfaceOnly:=True, _
        Password:=strPwd
End With

End Sub

Turn Excel AutoFilter On or Off

Use the following macro to turn an Excel AutoFilter on,
if no filter exists on the active sheet. Go to Top

Sub TurnAutoFilterOn()
'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Sub

Use the following macro to turn an Excel
AutoFilter off, if one exists on the active sheet

Sub TurnFilterOff()
'removes AutoFilter if one exists
  Worksheets("Data").AutoFilterMode = False
End Sub    

Ungroup Dates in Filter Drop Down

By default, when you turn on an AutoFilter, dates are grouped in
the drop down list. Click on a plus sign, to see the months for each year.

If you don’t want the dates grouped automatically, you can:

NOTE: If you have a copy of my Excel Tools add-in, use the Toggle Filter Grouping command on the Workbook Appearance drop down.

Macro to Turn Date Grouping On or Off

This code toggles the date grouping setting:

• if date grouping is
  on, the macro turns it off
• if date grouping is off, the macro turns it on

Sub ToggleFilterDateGroup()
    ActiveWindow.AutoFilterDateGrouping _
    = Not ActiveWindow.AutoFilterDateGrouping
End Sub

Hide AutoFilter Arrows

Perhaps you want users to filter only specific fields in a list. Use the following macros to hide one or more of the drop down arrows in the list heading row.

NOTE: These macros do not turn the AutoFilter off. They just change the VisibleDropDown property to False, for some fields.

Hide All Arrows

The following Excel AutoFilter VBA procedure hides the arrows for
all fields in the list. The Filter feature is NOT turned off.

Sub HideALLArrows()
'hides all arrows in heading row
'the Filter remains ON
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
i = 1
Application.ScreenUpdating = False

For Each c In rng.Cells
  c.AutoFilter Field:=i, _
      Visibledropdown:=False
  i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Hide All Arrows Except One

The following Excel AutoFilter VBA procedure hides the arrows for
all fields except field 2.

You can change the field number in the iShow variable, to leave a different field’s arrow visible.

NOTE: Use the Field number, NOT the worksheet column number.

Sub HideArrowsExceptOne()
'hides all arrows except
' in specified field number
Dim c As Range
Dim rng As Range
Dim i As Long
Dim iShow As Long
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
i = 1
iShow = 2 'leave this field's arrow visible
Application.ScreenUpdating = False

For Each c In rng.Cells
  If i = iShow Then
    c.AutoFilter Field:=i, _
      Visibledropdown:=True
  Else
      c.AutoFilter Field:=i, _
      Visibledropdown:=False
  End If
  i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Hide Arrows on Specific Fields

In some lists, you might want to hide the arrows on specific fields,
and leave all the other arrows visible. The following macro hides
the arrows for fields 1, 3 and 4 — Case 1, 3, 4

You can change the field numbers in the first Case statement, to hide different arrows.

NOTE: Use the Field number, NOT the worksheet column number.

Sub HideArrowsSpecificFields()
'hides arrows in specified fields
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
i = 1
Application.ScreenUpdating = False

For Each c In rng.Cells
  Select Case i
    Case 1, 3, 4
      c.AutoFilter Field:=i, _
        Visibledropdown:=False
    Case Else
      c.AutoFilter Field:=i, _
        Visibledropdown:=True
  End Select
  i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Show AutoFilter Arrows

If one or more of the AutoFilter arrows have been hidden, use the following macros to show the drop down arrows in the list heading row.

Show All AutoFilter Arrows

To show all the AutoFilter arrows again, use the following macro. When the macro runs, it shows the arrow for each cell in the list heading row.

Sub ShowALLArrows()
'shows all arrows in headng row
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
i = 1
Application.ScreenUpdating = False

For Each c In rng.Cells
  c.AutoFilter Field:=i, _
      Visibledropdown:=True
  i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Show All AutoFilter Arrows Except One

The following Excel AutoFilter VBA procedure shows the arrows for
all fields except field 2. You can change the field number in the iHide variable, to hide a different field’s arrow.

NOTE: Use the Field number, NOT the worksheet column number.

Sub ShowArrowsExceptOne()
'shows all arrows except
' in specified field number
Dim c As Range
Dim rng As Range
Dim i As Long
Dim iHide As Long
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
i = 1
iHide = 3 'leave this field's arrow hidden
Application.ScreenUpdating = False

For Each c In rng.Cells
  If i = iHide Then
    c.AutoFilter Field:=i, _
      Visibledropdown:=False
  Else
      c.AutoFilter Field:=i, _
      Visibledropdown:=True
  End If
  i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Copy Filtered Rows

The following macro copies the filtered rows from
the active sheet AutoFilter to a sheet named “Sheet2”.

Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With
If rng2 Is Nothing Then
   MsgBox "No data to copy"
Else
   Worksheets("Sheet2").Cells.Clear
   Set rng = ActiveSheet.AutoFilter.Range
   rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
     Destination:=Worksheets("Sheet2").Range("A1")
End If
   ActiveSheet.ShowAllData

End Sub    

Use AutoFilter on Protected Worksheet

You can use an Excel AutoFilter on a protected worksheet, but you
can’t create an Excel AutoFilter on a protected worksheet. Be sure
that the filter is in place before you protect the sheet.

To allow users to use
AutoFilter after the sheet is protected, be sure to add a check mark to the Use AutoFilter box, when you protect the sheet

Turn on AutoFilter and Protect Sheet

To be sure that a sheet has an AutoFilter, and the sheet is protected, use a macro that runs automatically when the workbook is opened, and macros are enabled.

This Workbook_Open macro checks for an AutoFilter on the sheet named Data. It
turns on the AutoFilter, if one is not in place. Then, it protects the Data sheet, and
sets the protection to user interface only. That allows macros to change the sheet, but users cannot make changes manually.

Store the following Excel
AutoFilter macro on the ThisWorkbook module sheet. There are instructions below the code.

Private Sub Workbook_Open() 
'check for filter, turn on if none exists
With Worksheets("Data")
  If Not .AutoFilterMode Then
    .Range("A1").AutoFilter
  End If
  .EnableAutoFilter = True
  .Protect Password:="password", _
  Contents:=True, UserInterfaceOnly:=True
End With
End Sub 

Add the Workbook Open Code

To add this code to your workbook:

• To open the ThisWorkbook module, press Alt+F11, to open the Visual Basic Editor.
• Then, in the Project Explorer at the left, find your workbook, and click the + sign to see the Microsoft Excel Objects.
• Right-click on ThisWorkbook, and click View Code
• Then, paste the
  code where the cursor is flashing.

Count Visible Rows

With this Excel AutoFilter VBA sample code, show a message
with a count of the rows that are visible after a filter has
been applied.

Sub CountVisRows()
'by Tom Ogilvy
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

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

End Sub

Check For AutoFilter

To see if a worksheet contains a worksheet AutoFilter, run this macro. If there is a worksheet AutoFilter on the active sheet, this code
will print a line in the Immediate window, with a count of one. You could use this type of code in other macros, to check a specific sheet for an AutoFilter.

Note: While you are in the Visual Basic Editor, press Ctrl+G to show the Immediate Window.

Sub CountSheetAutoFilters()
Dim iARM As Long
'counts all worksheet autofilters
'even if all arrows are hidden
  If ActiveSheet.AutoFilterMode = True Then iARM = 1
  Debug.Print "AutoFilterMode: " & iARM
End Sub     

Get the Macro Workbook

To see all the macros from this page, download the AutoFilter Worksheet Macros workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.

More Tutorials

Excel List AutoFilter VBA

Excel AutoFilter Basics