Contextures

Excel AutoFilter Macros

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

Show All Records go to top

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 go to top

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

Show All Records on Password Protected Sheet go to top

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 On Excel AutoFilter go to top

Use the following Excel AutoFilter VBA code to turn on an Excel AutoFilter, if none exists

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

Turn Off Excel AutoFilter go to top

Use the following Excel AutoFilter VBA code to turn off an Excel AutoFilter, if one exists

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. You can manually change a setting, to ungroup them, or use programming to turn the grouping on or off.

This code toggles the date grouping setting -- if the grouping is on, it turns it off, and if grouping is off, the code turns it on.

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

Count Worksheet AutoFilters go to top

If there is a worksheet AutoFilter on the active sheet, this code will return a count of one.

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     

Hide Excel AutoFilter Arrows go to top

Perhaps you want users to filter only one of the columns in a table. The following Excel AutoFilter VBA procedure hides the arrows for all columns except column 2.

Sub HideArrows() 
'hides all arrows except column 2
Dim c As Range 
Dim i As Integer 
i = Cells(1, 1).End(xlToRight).Column 
Application.ScreenUpdating = False 

For Each c In Range(Cells(1, 1), Cells(1, i))
 If c.Column <> 2 Then 
  c.AutoFilter Field:=c.Column, _ 
    Visibledropdown:=False 
 End If 
Next 

Application.ScreenUpdating = True 
End Sub 

In other cases, you might want to hide the arrows on specific columns, and leave all the other arrows visible. The following procedure hides the arrows for columns 1, 3 and 4.

Sub HideSpecifiedArrows()
'hides arrows in specified columns
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

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

Application.ScreenUpdating = True
End Sub

To show all the arrows, you can use the following Excel AutoFilter VBA code:

Sub ShowArrows()
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
c.AutoFilter Field:=c.Column, _
  Visibledropdown:=True
Next
Application.ScreenUpdating = True
End Sub

If your table doesn't start in cell A1, you can specify the heading range, and hide arrows in specific cells in that range.

Sub HideArrowsRange()
'hides arrows in specified range
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = Range("D14:J14")
i = rng.Cells(1, 1).Column - 1
Application.ScreenUpdating = False

For Each c In Range("D14:J14")
    Select Case c.Address
    Case "$E$14", "$G$14", "$J$14"
        c.AutoFilter Field:=c.Column - i, _
          Visibledropdown:=False
    Case Else
        c.AutoFilter Field:=c.Column - i, _
          Visibledropdown:=True
    End Select
Next

Application.ScreenUpdating = True
End Sub

If your table doesn't start in cell A1, you can specify the heading range, and show all the arrows in that range.

Sub ShowArrowsRange()
'shows arrows in specified range
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = Range("D14:J14")
i = rng.Cells(1, 1).Column - 1
Application.ScreenUpdating = False
For Each c In Range("D14:J14")
    c.AutoFilter Field:=c.Column - i, _
      Visibledropdown:=True
Next
Application.ScreenUpdating = True
End Sub

Copy Filtered Rows go to top

The followingExcel AutoFilter VBA code copies the filtered rows from the active sheet to 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    

Excel AutoFilter on a Protected Worksheet go to top

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

You can allow users to use AutoFilter when you set the worksheet protection -- add a check mark to the Use AutoFilter box.

Allow AutoFilter on protected sheet

You can also use a Workbook_Open macro to set the protection to user interface only. Store the following Excel AutoFilter VBA code on the ThisWorkbook module sheet. It also turns on the AutoFilter is one is not in place:

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:

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

ThisWorkbook View Code

Count Visible Rows go to top

With this Excel AutoFilter VBA sample code, you can display a message that shows a count of the rows that are visible after a filter has been applied:

message shows count of visible records

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

More Tutorials

Excel List AutoFilter VBA

Excel AutoFilter Basics

 

Custom Search

 

30 Excel Functions in 30 Days

 

 

 

 

Excel Tools

 

 

 

 

 

 

Pivot Power Premium

 

 

Excel chart tools

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

Excel tools

 

 

Online Excel Training

 Get Excel News

 

 

 


 

 

30 Excel Functions in 30 Days

 

 


 

 


Last updated: September 11, 2016 4:00 PM