Search Contextures Sites

 

Excel -- Filters -- AutoFilter Programming

AutoFilter Basics

A few examples of controlling AutoFilters through VBA programming.

Show All Records
Turn On AutoFilter
Turn Off AutoFilter
Hide Filter Arrows
Copy Filtered Rows
AutoFilter on a Protected Worksheet
Count Visible Rows

 

 

  


Show All Records

The following code shows all records, if a filter has been applied.

Sub ShowAllRecords()
  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If
End Sub






Turn On AutoFilter

Use the following code to turn on an 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 AutoFilter

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

Sub TurnFilterOff()
'removes AutoFilter if one exists
  Worksheets("Data").AutoFilterMode = False
End Sub






Hide Filter Arrows

Perhaps you want users to filter only one of the columns in a table. The following 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 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 following 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






AutoFilter on a Protected Worksheet

You can use an AutoFilter on a protected worksheet, but you can't create an AutoFilter. 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 at right)

In previous versions of Excel, use a Workbook_Open macro to set the protection to user interface only. Store the following 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

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. AutoFilter Basics
  2. AutoFilter Tips
  3. AutoFilter Programming
   

       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store