Home > Filters > AutoFilter > Macros Excel AutoFilter MacrosExamples of AutoFilter macros, for use with worksheet AutoFilters (only one worksheet AutoFilter is allowed per worksheet). NOTE: For Excel table filter macros, go to this page: List AutoFilter Macros |
The following Excel AutoFilter VBA code shows all records, if a filter has been applied.
Sub ShowAllRecords() With ActiveSheet.AutoFilter If .FilterMode Then .ShowAllData End If End With End Sub
The following macros are designed for sheets that are protected. There are two versions of the macro:
If the worksheet is protected, with no password, use this Excel VBA 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
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
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
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:
This code toggles the date grouping setting:
Sub ToggleFilterDateGroup() ActiveWindow.AutoFilterDateGrouping _ = Not ActiveWindow.AutoFilterDateGrouping End Sub
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.
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
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
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 number of the column on the worksheet.
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
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.
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
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
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
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
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
To add this code to your workbook:
With this Excel AutoFilter VBA sample code, show a message with a count of the rows that are visible after a filter with criteria 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
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
This video shows how to copy macro code to a regular code module in your workbook. For written steps, go to the Copy Macro Code page.
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.
Last updated: December 31, 2022 3:41 PM