Search Contextures Sites

Excel Filters -- Excel AutoFilter VBA

Excel AutoFilter Basics

A few examples of Excel AutoFilter VBA programming.

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

 

 

  


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






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






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 at right)

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
   

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.