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
