ListObject AutoFilter FilterMode

[wsnyder]

Hi all,

Using Excel 365.

My snippet is throwing an error here

If lo.FilterMode Then

Not sure why that is?

Thanks
-w

Full snippet:

Sub ShowAll_Lists()

Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    For Each lo In ws.ListObjects
        With lo.AutoFilter
            If lo.FilterMode Then
                lo.ShowAllData
            Else
                lo.ShowAutoFilter = True
            End If
        End With
    Next lo
Next ws
Set wb = Nothing

End Sub

 

[antman2988]

If lo.AutoFilterMode Then

 

[wsnyder]

Looks like I solved it
This will show all if the Table is filtered and add filter buttons if the Table is not filtered

thanks,
-w

Sub ShowAll_Lists()

Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    For Each lo In ws.ListObjects
        If Not lo.AutoFilter Is Nothing Then
            lo.AutoFilter.ShowAllData
        Else
            lo.ShowAutoFilter = True
        End If
    Next lo
Next ws
Set wb = Nothing

End Sub

 

[DanteAmor]

Here another option to consider:

Sub ShowAll_Lists()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim lo As ListObject
  
  Set wb = ThisWorkbook

  For Each ws In wb.Worksheets
    For Each lo In ws.ListObjects
      If lo.ShowAutoFilter Then
        lo.AutoFilter.ShowAllData
      Else
        lo.Range.AutoFilter
      End If
    Next lo
  Next ws
End Sub

 

[antman2988]

Here another option to consider:

Sub ShowAll_Lists()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim lo As ListObject
 
  Set wb = ThisWorkbook

  For Each ws In wb.Worksheets
    For Each lo In ws.ListObjects
      If lo.ShowAutoFilter Then
        lo.AutoFilter.ShowAllData
      Else
        lo.Range.AutoFilter
      End If
    Next lo
  Next ws
End Sub

You can also do it with just

If lo.AutoFilterMode Then

, which is what I posted above. It'll tell you if it's on.

 

[DanteAmor]

If lo.AutoFilterMode Then

which is what I posted above. It'll tell you if it's on.

With that line I get the error:
"Object Doesn't Support this Property or Method"


.AutoFilterMode, works with the filter of a range in the sheet, but not with the filter of a list_object.