|
Home > Filters > AutoFilter > Macros Excel List AutoFilter MacrosExamples of Excel List AutoFilter macros, to use with the filters found in named Excel table headings. NOTE: For worksheet AutoFilter macros, go to this page: Worksheet AutoFilter Macros
|
The following macros are for use with the filters in the heading cells of named Excel tables. Each named table is a ListObject, and has its own AutoFilter property. There can be multiple List AutoFilters on a single worksheet.
Also see: Excel Worksheet AutoFilter VBA and Excel AutoFilter Basics
The following macro shows all records in List 1 on the active sheet, if a filter has been applied.
Sub ShowAllRecordsList1()
'shows all records in list 1,
' if filters were applied
Dim Lst As ListObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set Lst = ws.ListObjects(1)
With Lst.AutoFilter
If .FilterMode Then
.ShowAllData
End If
End With
End Sub
Use the following macro to clear the filters on all the selected columns in the Excel Table.
For example, in this table, there are filters on solumns E, F and G. Cells are selected in columns F and G, so only those filters will be cleared.
Note: In the sample workbook, this macro, ClearFilterColSel, runs on the keyboard shortcut:

Sub ClearFilterColSel()
'shortcut Ctrl+Shift+C
'clears filters in selected columns
Dim myList As ListObject
Dim myDBR As Range
Dim mySel As Range
Dim myInt As Range
Dim c As Range
Dim SheetCol As Long
Dim StartCol As Long
Dim FltrCol As Long
Dim myMsg As String
myMsg = "Could not clear filters" _
& vbCrLf _
& "in selected columns"
On Error Resume Next
Set mySel = Selection
Set myList = mySel.Cells(1).ListObject
On Error GoTo errHandler
If myList Is Nothing Then
myMsg = "Select a table cell" _
& vbCrLf _
& "and try again"
GoTo errHandler
End If
Set myDBR = myList.DataBodyRange
StartCol = myDBR.Columns(1).Column
For Each c In mySel
On Error Resume Next
Set myInt = _
Application.Intersect(c, myDBR)
On Error GoTo errHandler
If myInt Is Nothing Then
GoTo errHandler
End If
SheetCol = c.Column
FltrCol = SheetCol - StartCol + 1
myList.Range.AutoFilter _
Field:=FltrCol
Next c
exitHandler:
Set myList = Nothing
Exit Sub
errHandler:
MsgBox myMsg
Resume exitHandler
End Sub
Use the following Excel AutoFilter VBA code to turn on an Excel AutoFilter in List 1 on the active sheet.
Sub TurnAutoFilterOnList1()
'turn on AutoFilter for List 1
Dim Lst As ListObject
Set Lst = ActiveSheet.ListObjects(1)
Lst.ShowAutoFilter = True
End Sub
Use the following macro to turn off an Excel AutoFilter in List 1 on the active sheet.
Sub TurnAutoFilterOffList1() 'turn off AutoFilter in List 1 Dim Lst As ListObject Set Lst = ActiveSheet.ListObjects(1) Lst.ShowAutoFilter = False End Sub
To count all the Lists and Named Tables on a worksheet, where AutoFilters are active, you can use the following code.
Sub CountListAutoFilters()
'counts list autofilters even if all arrows are hidden
Dim Lst As ListObject
Dim i As Long
i = 0
For Each Lst In ActiveSheet.ListObjects
If Lst.ShowAutoFilter = True Then
i = i + 1
End If
Next Lst
Debug.Print "List AutoFilters: " & i
End Sub
Perhaps you want users to filter only one of the columns in List 1. The following Excel AutoFilter VBA procedure hides the arrows for all columns except the second column in List 1
Sub HideArrowsList1()
'hides all arrows except list 1 column 2
Dim Lst As ListObject
Dim c As Range
Dim i As Integer
Application.ScreenUpdating = False
Set Lst = ActiveSheet.ListObjects(1)
i = 1
For Each c In Lst.HeaderRowRange
If i <> 2 Then
Lst.Range.AutoFilter Field:=i, _
VisibleDropDown:=False
Else
Lst.Range.AutoFilter Field:=i, _
VisibleDropDown:=True
End If
i = i + 1
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 in List 2.
Sub HideSpecifiedArrowsList2()
'hides arrows in specified columns in List 2
Dim Lst As ListObject
Dim c As Range
Dim i As Integer
Application.ScreenUpdating = False
Set Lst = ActiveSheet.ListObjects(2)
i = 1
For Each c In Lst.HeaderRowRange
Select Case i
Case 1, 3, 4
Lst.Range.AutoFilter Field:=i, _
Visibledropdown:=False
Case Else
Lst.Range.AutoFilter Field:=i, _
Visibledropdown:=True
End Select
i = i + 1
Next
Application.ScreenUpdating = True
End Sub
To show all the arrows in List 1, you can use the following Excel AutoFilter VBA code:
Sub ShowArrowsList1()
Dim Lst As ListObject
Dim c As Range
Dim i As Integer
Application.ScreenUpdating = False
Set Lst = ActiveSheet.ListObjects(1)
i = 1
For Each c In Lst.HeaderRowRange
Lst.Range.AutoFilter Field:=i, _
Visibledropdown:=True
i = i + 1
Next
Application.ScreenUpdating = True
End Sub
Select a cell in a named Excel table that has filters applied. Then, run the ActiveListFilterInfo macro, to see a message box, with details on the filters that have been applied.

This macro will work correctly on a worksheet that has a single named Excel table, or multiple named Excel tables.
NOTE: The message box shows "Unknown" for columns where items were selected from the' Search box, or in the item check list.
Sub ActiveListFilterInfo()
'show current filter settings
Dim Lst As ListObject
Dim LstHd As Range
Dim c As Range
Dim i As Integer
Dim ListCols As Long
Dim afCount As Long
Dim myLst As ListObject
Dim myFilt As Filter
Dim afCrit1 As String
Dim afCrit2 As String
Dim afOp As String
Dim lngOp As Long
Dim afMsg As String
Application.ScreenUpdating = False
On Error Resume Next
Set Lst = ActiveCell.ListObject
If Lst Is Nothing Then
MsgBox "Select cell in list," _
& vbCrLf _
& "then try again"
Exit Sub
End If
If Lst.ShowAutoFilter = False Then
MsgBox "No active filters applied"
Exit Sub
End If
Set LstHd = Lst.HeaderRowRange
ListCols = LstHd.Columns.Count
For i = 1 To ListCols
Set myFilt = Lst.AutoFilter.Filters.Item(i)
If Not myFilt.On Then
'do nothing
Else
afMsg = afMsg & _
Format(i, "000") & ") " _
& LstHd.Cells(i).Value _
& ": "
afCrit1 = myFilt.Criteria1
afCrit2 = myFilt.Criteria2
lngOp = myFilt.Operator
Select Case lngOp
Case xlAnd: afOp = " AND "
Case xlOr: afOp = " OR "
Case Else: afOp = ""
End Select
If afCrit1 = "" Then _
afCrit1 = "Unknown"
afMsg = afMsg & afCrit1 _
& afOp & afCrit2
afCount = afCount + 1
afMsg = afMsg & vbCrLf
End If
Next i
Application.ScreenUpdating = True
If afCount = 1 Then
MsgBox "There is 1 filtered column:" _
& vbCrLf & vbCrLf & afMsg
Else
MsgBox "There are " & afCount _
& " filtered columns:" _
& vbCrLf & vbCrLf & afMsg
End If
End Sub
The following macro copies the filtered rows, but not the headings, from List1 on the active sheet, to a new worksheet.
Sub CopyFilteredRowsOnlyList1()
Dim wsL As Worksheet
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim Lst As ListObject
Application.ScreenUpdating = False
Set wsL = ActiveSheet
Set Lst = wsL.ListObjects(1)
With Lst.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
Set ws = Sheets.Add
Set rng = Lst.AutoFilter.Range
'copy rows without headings
rng.Offset(1, 0).Resize(rng.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ws.Range("A1")
End If
Application.ScreenUpdating = True
End Sub
The following macro copies the filtered rows, and the headings, from List1 on the active sheet, to a new worksheet.
Sub CopyFilteredRowsAndHeadingsList1()
Dim wsL As Worksheet
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim Lst As ListObject
Application.ScreenUpdating = False
Set wsL = ActiveSheet
Set Lst = wsL.ListObjects(1)
With Lst.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
Set ws = Sheets.Add
Set rng = Lst.AutoFilter.Range
'copy rows with headings
rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ws.Range("A1")
End If
Application.ScreenUpdating = True
End Sub
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 CountVisibleRowsList1() Dim Lst As ListObject Set Lst = ActiveSheet.ListObjects(1) Dim rng As Range Set rng = Lst.AutoFilter.Range MsgBox rng.Columns(1). _ SpecialCells(xlCellTypeVisible).Count - 1 _ & " of " & rng _ .Rows.Count - 1 & " Records" End Sub
To see all the macros from this page, download the List AutoFilter Macros workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.
Last updated: January 25, 2023 2:30 PM