Search Contextures Sites

 

 

Contextures
Excel news
by email

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

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
Ungroup Dates in Filter Drop Down
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    

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

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

 

Learn how to create Excel dashboards.

  1. Excel AutoFilter Basics
  2. Excel AutoFilter Tips
  3. Excel AutoFilter VBA
  4. Excel AutoFilter Lists VBA
   

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.