Excel Filters -- AutoFilter VBA

Search Contextures Sites
Contextures
Excel news
by email

Email:

Name (optional):

We respect your email privacy

Time-saving
Pivot Table add-in
Excel Filters -- AutoFilter VBA

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

Show All Records
Show All Records on Protected Sheet
Show All Records on Password Protected Sheet
Turn On Excel AutoFilter
Turn Off Excel AutoFilter
Count Worksheet AutoFilters
Hide Excel AutoFilter Arrows
Copy Filtered Rows
Excel AutoFilter on a Protected Worksheet
Count Visible Rows

Also see: Excel List AutoFilter VBA
and Excel AutoFilter Basics

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

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

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

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

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    
Count Worksheet AutoFilters

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

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

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

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.

In Excel 2002 and later versions, you can allow users to use AutoFilter when you set the worksheet protection. (see Protect Sheet dialog box below)

In previous versions of Excel, 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 access the ThisWorkbook module, right-click the Excel icon to the left of the File menu, choose View Code, and paste the code where the cursor is flashing.

Count Visible Rows

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:
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
Excel AutoFilter Basics

Excel AutoFilter Tips

Excel AutoFilter VBA

Excel AutoFilter Lists VBA

Home Excel Tips Excel Files The Excel Store Blog Contact

Privacy Policy

Contextures Inc., Copyright �2013
All rights reserved.

Debra Dalgleish

Contextures Excel news by email Email: Name (optional): We respect your email privacy Time-saving Pivot Table add-in		Excel Filters -- AutoFilter VBA Examples of AutoFilter VBA programming, for use with worksheet AutoFilters (only one allowed per worksheet). Show All Records Show All Records on Protected Sheet Show All Records on Password Protected Sheet Turn On Excel AutoFilter Turn Off Excel AutoFilter Count Worksheet AutoFilters Hide Excel AutoFilter Arrows Copy Filtered Rows Excel AutoFilter on a Protected Worksheet Count Visible Rows Also see: Excel List AutoFilter VBA and Excel AutoFilter Basics 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 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 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 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 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 Count Worksheet AutoFilters 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 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 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 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. In Excel 2002 and later versions, you can allow users to use AutoFilter when you set the worksheet protection. (see Protect Sheet dialog box below) In previous versions of Excel, 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 access the ThisWorkbook module, right-click the Excel icon to the left of the File menu, choose View Code, and paste the code where the cursor is flashing. Count Visible Rows 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: 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

Excel AutoFilter Basics Excel AutoFilter Tips Excel AutoFilter VBA Excel AutoFilter Lists VBA