Find Table objects in the active sheet to remove auto filter

user_731043 0
Viewed 113

I need to find the one table (one worksheet will only have one Table) in the Active worksheet and find the first & last column in that table and remove the auto filter from those first and last columns.

Currently I am doing this:- The problem is I have to manually input the Field values as the name of the sheet will change as well as the name of the table

Private Sub Worksheet_Activate()
' Select middle cell
ActiveSheet.Range("$A$1").Select
' Remove autofilter from first column in the table
With Range("$A$2")
    .AutoFilter Field:=1, VisibleDropDown:=False
End With
' Remove autofilter from last column in the table
With Range("$Q$2")
    .AutoFilter Field:=17, VisibleDropDown:=False
End With
End Sub
excel-tables autofilter vba excel
2 Answers
Private Sub Worksheet_Activate()
     
    With Me.ListObjects
        If .Count = 1 Then
            With .Item(1).Range
                .AutoFilter Field:=1
                .AutoFilter Field:=.Columns.Count
            End With
        End If
    End With

End Sub
user_127045930

Remove Filters From Columns of an Excel Table (Workbook_SheetActivate)

  • Copy the code into the ThisWorkbook module.
  • When activating (selecting) another worksheet, if it contains an Excel table, the filters in the first and last columns will be removed so will their dropdown arrows.
  • To test it, filter a table by the first, by another, and by the last columns. After selecting another worksheet and then selecting the first worksheet, only the filter of the other column remains.
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    Dim tbl As ListObject
    On Error Resume Next
        Set tbl = Sh.ListObjects(1)
    On Error GoTo 0
    
    If Not tbl Is Nothing Then
        With tbl.Range
            .Columns(1).AutoFilter 1, VisibleDropdown:=False
            .Columns(.Columns.Count).AutoFilter .Columns.Count, _
                VisibleDropdown:=False
        End With
    End If

End Sub

Edit: Excluding Worksheets

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    ' To exclude charts (kind of covered with 'On Error' statement).
    'If sh.Type <> xlWorksheet Then Exit Sub 
    
    Dim Exceptions As Variant: Exceptions = Array("Sheet1", "Sheet2")

    ' To exclude the sheets in the list:    
    If IsError(Application.Match(Sh.Name, Exceptions, 0)) Then
    ' To restrict to the sheets in the list:
    'If IsNumeric(Application.Match(Sh.Name, Exceptions, 0)) Then
        
        Dim tbl As ListObject
        On Error Resume Next
            Set tbl = Sh.ListObjects(1)
        On Error GoTo 0
        
        If Not tbl Is Nothing Then
            With tbl.Range
                .Columns(1).AutoFilter 1, VisibleDropdown:=False
                .Columns(.Columns.Count).AutoFilter .Columns.Count, _
                    VisibleDropdown:=False
            End With
        End If

    End If

End Sub
user_98140690
Related