6 Jul 2016

VBA Subroutine to Clear All Filters in Excel-Table


Sub filter_clear()

'****************************************************************************************************************
'author:    kay cichini
'date:      06072016
'purpose:   clear all filters in a preformated excel tabel
'****************************************************************************************************************

Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject

Set SelectedCell = ActiveCell

'Determine if ActiveCell is inside a Table
  On Error GoTo NoTableSelected
    TableName = SelectedCell.ListObject.Name
    Set ActiveTable = ActiveSheet.ListObjects(TableName)

    If ActiveTable.ShowAutoFilter Then
      'MsgBox "AutoFilters are turned on"
      If ActiveTable.AutoFilter.FilterMode Then
        'MsgBox "Filter is actually set"
        ActiveTable.AutoFilter.ShowAllData
      End If
    End If
  On Error GoTo 0

Exit Sub

'Error Handling
NoTableSelected:
  MsgBox "There is no Table currently selected! (You need to activate a cell in the Table to be cleared!)", vbCritical

End Sub