Search Contextures Sites

 

 

 

 

Excel List AutoFilter VBA

Examples of Excel List AutoFilter VBA programming, for use with the List AutoFilters found in named Excel tables (multiple allowed per worksheet). Each named table is a ListObject, and has its own AutoFilter property.

Show All Records
Turn On List AutoFilter
Turn Off List AutoFilter
Count List AutoFilters
Hide All List AutoFilter Arrows Except One
Hide List AutoFilter Arrows Specific Columns
Show All List AutoFilter Arrows
Copy Filtered List Rows Without Headings
Copy Filtered List Rows With Headings
Count Visible List Rows

Also see: Excel Worksheet AutoFilter VBA and Excel AutoFilter Basics

Show All Records

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 applied
Dim Lst As ListObject


Set Lst = ActiveSheet.ListObjects(1)
  If Lst.AutoFilter.FilterMode Then
    Lst.AutoFilter.ShowAllData
  End If
End Sub

Turn On List AutoFilter

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

Turn Off List AutoFilter

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

Count List AutoFilters

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

Hide All List AutoFilter Arrows Except One

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

Hide List AutoFilter Arrows Specific Columns

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
EndSub

Show All List AutoFilter Arrows

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

Copy Filtered List Rows Without Headings

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

Copy Filtered List Rows With Headings

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

Count Visible List 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 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
 

Learn how to create Excel dashboards.

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

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2012
All rights reserved.