excelvbaautofilterexcel-tableslistobject

Find Table objects in the active sheet to remove auto filter


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

Solution

  • Remove Filters From Columns of an Excel Table (Workbook_SheetActivate)

    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