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
Workbook_SheetActivate
)ThisWorkbook
module.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