Excel 2013 VBA Clear All Filters macro

Question

It seems older macros are not working. I have proper securtiy set to run VBA macros but when I have tried a few methods for clearing ALL filters on a worksheet, I get a compile error.

Here is what I have tried:

Sub AutoFilter_Remove()
'This macro removes any filtering in order to display all of the data but it does not remove the filter arrows
ActiveSheet.ShowAllData
End Sub

I have buttons on the sheets to clear all filters for ease of use for users since the sheets has a lot of columns that have filters on them.

Answer 1

If the sheet already has a filter on it then:

Sub Macro1()
    Cells.AutoFilter
End Sub

will remove it.

Answer 2

Try this:

If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

Answer 3

ShowAllData will throw an error if a filter isn't currently applied. This will work:

Sub ResetFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Answer 4

For tables try this to check if it's on and turn off:

If wrkSheetCodeName.ListObjects("TableName").ShowAutoFilter Then
    wrkSheetCodeName.ListObjects("TableName").Range.AutoFilter
End if

To Turn back on:

wrkSheetCodeName.ListObjects("TableName").Range.AutoFilter

Answer 5

this works nice.!

If ActiveSheet.AutoFilterMode Then Cells.AutoFilter

Answer 6

That is brilliant, the only answer I found that met my particular need, thanks SO much for putting it up!

I made just a minor addition to it so that the screen didn't flash and it removes and subsequently reapplies the password on each sheet as it cycles through [I have the same password for all sheets in the workbook]. In the spirit of your submission, I add this to assist anyone else....

Sub ClearFilters()
    Application.ScreenUpdating = False

    On Error Resume Next
    For Each wrksheet In ActiveWorkbook.Worksheets
        'Change the password to whatever is required
        wrksheet.Unprotect Password:="Albuterol1"
        wrksheet.ShowAllData 'This works for filtered data not in a table

        For Each lstobj In wrksheet.ListObjects
            If lstobj.ShowAutoFilter Then
                lstobj.Range.AutoFilter 'Clear filters from a table
                lstobj.Range.AutoFilter 'Add the filters back to the table
            End If

        'Change the password to whatever is required
        wrksheet.Protect Password:="Albuterol1", _
              DrawingObjects:=True, _
              Contents:=True, _
              Scenarios:=True, _
              AllowFiltering:=True
        Next 'Check next worksheet in the workbook
    Next

    Application.ScreenUpdating = True
End Sub

I know this is a relatively old post and don't really like being a necromancer... But since I had the same issue and tried a few of the options in this thread without success I combined some of the answers to get a working macro..

Hopefully this helps someone out there :)

Sub ResetFilters()
    On Error Resume Next
    For Each wrksheet In ActiveWorkbook.Worksheets
        wrksheet.ShowAllData 'This works for filtered data not in a table
        For Each lstobj In wrksheet.ListObjects
            If lstobj.ShowAutoFilter Then
                lstobj.Range.AutoFilter 'Clear filters from a table
                lstobj.Range.AutoFilter 'Add the filters back to the table
            End If
        Next 'Check next worksheet in the workbook
    Next
End Sub

Answer 7

There are two types of filters in Excel:

• Auto Filter
• Advanced Filter

The Auto Filter feature lets you filter from the excel interface using those tiny dropdown buttons. And the Advanced filter feature lets you filter using a criteria range.

The ShowAll method removes the filters, as in, shows all the rows, but does not get rid of those Drop Down buttons. You have to set the AutoFilterMode property of the worksheet to FALSE to remove those buttons.

Here is a Sub that I use frequently to remove filters:

Sub RemoveFilters(ByRef WhichSheet As Worksheet)

If WhichSheet.FilterMode Then WhichSheet.ShowAllData
If WhichSheet.AutoFilterMode Then WhichSheet.AutoFilterMode = False

End Sub

This shows all the data, and removes the dropdown buttons. It comes in handy while stacking (copying and pasting) data from multiple sheets or workbooks. Hope this helps.

Answer 8

I found this workaround to work pretty effectively. It basically removes autofilter from the table and then re-applies it, thus removing any previous filters. From my experience this is not prone to the error handling required with the other methods mentioned here.

Set myTable = YOUR_SHEET.ListObjects("YourTableName")

myTable.ShowAutoFilter = False
myTable.ShowAutoFilter = True

Answer 9

This will work too:

If ActiveSheet.FilterMode Then cells.AutoFilter

Answer 10

I usually use this code

Sub AutoFilter_Remove()
    Sheet1.AutoFilterMode = False  'Change Sheet1 to the relevant sheet
                                   'Alternatively: Worksheets("[Your Sheet Name]").AutoFilterMode = False
End Sub

Answer 11

This will first check if AutoFilterMode is set (filtering is possible), then check if FilterMode is on (you are filtering on something) then turn off filtering.

Regarding Errors, i.e. protection - se other answers

Context added (my script is looping over sheets, which are then saved as CSV, hence the need to remove filters - but keep AutoFilterMode on, if set:

For Each WS In ActiveWorkbook.Worksheets
  Select Case WS.Name
    Case "01", "02", "03", "04", "05"
      With WS
        If WS.AutoFilterMode Then
            If WS.FilterMode Then WS.ShowAllData
        End If

        ' Processing data
      End With
    Case Else
      ' Nothing to see here
  End Select
Next

Answer 12

Try something like this:

Sub ClearDataFilters()
'Clears filters on the activesheet. Will not clear filters if the sheet is protected.
On Error GoTo Protection
If ActiveWorkbook.ActiveSheet.FilterMode Or _
   ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
   ActiveWorkbook.ActiveSheet.ShowAllData

Exit Sub
Protection:
If Err.Number = 1004 And Err.Description = _ 
    "ShowAllData method of Worksheet class failed" Then
    MsgBox "Unable to Clear Filters. This could be due to protection on the sheet.", _
    vbInformation
End If

End Sub

.FilterMode returns true if the worksheet is in filter mode. (See this for more information.)
See this for more information on .AutoFilter.
And finally, this will provide more information about the .ShowAllData method.

Answer 13

Here's the one-liner I use. It checks for an auto-filter and if found, removes it.

Unlike some answers, this code won't create an auto-filter if used on a worksheet that is not auto-filtered in the first place.

If Cells.AutoFilter Then Cells.AutoFilter

Answer 14

All you need is:

    ActiveSheet.AutoFilter.ShowAllData

Why? Like the worksheet, AutoFilter also has a ShowAllData method, but it doesn't throw an error even when auto filter is enabled without an active filter.

Answer 15

This works best for me.

I usually use the following before I save and close the files.

Sub remove_filters

ActiveSheet.AutofilterMode = False

End Sub

Answer 16

Simply activate the filter headers and run showalldata, works 100%. Something like:

Range("A1:Z1").Activate
ActiveSheet.ShowAllData

Range("R1:Y1").Activate
ActiveSheet.ShowAllData

If you have the field headers in A1:Z1 and R1:Y1 respectively.

Answer 17

Im using .filtermode if filter is on it returns true

Dim returnValue As Boolean
    returnValue = worksheet1.FilterMode

    if returnValue Then
    worksheet1.ShowAllData
    End If

Answer 18

Try this:

Sub ResetFilters()
    Dim ws                    As Worksheet
    Dim wb                    As Workbook
    Dim listObj               As ListObject

    For Each ws In ActiveWorkbook.Worksheets
        For Each listObj In ws.ListObjects
            If listObj.ShowHeaders Then
                listObj.AutoFilter.ShowAllData
                listObj.Sort.SortFields.Clear
            End If
        Next listObj
    Next ws
End Sub

This Code clears all filters and removes sorting.

Source: Removing Filters for Each Table in a Workbook, VBA

Answer 19

Here is some code for fixing filters. For example, if you turn on filters in your sheet, then you add a column, then you want the new column to also be covered by a filter.

Private Sub AddOrFixFilters()

    ActiveSheet.UsedRange.Select

    ' turn off filters if on, which forces a reset in case some columns weren't covered by the filter
    If ActiveSheet.AutoFilterMode Then
        Selection.AutoFilter
    End If

    ' turn filters back on, auto-calculating the new columns to filter
    Selection.AutoFilter

End Sub

Answer 20

This thread is ancient, but I wasn't happy with any of the given answers, and ended up writing my own. I'm sharing it now:

We start with:

  Sub ResetWSFilters(ws as worksheet)
             If ws.FilterMode Then   
     ws.ShowAllData   
     Else   
     End If  
    'This gets rid of "normal" filters - but tables will remain filtered
    For Each listObj In ws.ListObjects 
               If listObj.ShowHeaders Then   
                    listObj.AutoFilter.ShowAllData
                    listObj.Sort.SortFields.Clear    
               End If     
       Next listObj
        'And this gets rid of table filters
        End Sub

We can feed a specific worksheet to this macro which will unfilter just that one worksheet. Useful if you need to make sure just one worksheet is clear. However, I usually want to do the entire workbook

Sub ResetAllWBFilters(wb as workbook)
  Dim ws As Worksheet  
  Dim wb As Workbook  
  Dim listObj As ListObject    

       For Each ws In wb.Worksheets  
          If ws.FilterMode Then 
          ws.ShowAllData  
          Else   
          End If   
 'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
   For Each listObj In ws.ListObjects 
        If listObj.ShowHeaders Then   
             listObj.AutoFilter.ShowAllData 
             listObj.Sort.SortFields.Clear    
        End If     
   Next listObj

        Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub

You can use this, by, for example, opening a workbook you need to deal with and resetting their filters before doing anything with it:

Sub ExampleOpen()
Set TestingWorkBook = Workbooks.Open("C:\Intel\......") 'The .open is assuming you need to open the workbook in question - different procedure if it's already open
Call ResetAllWBFilters(TestingWorkBook)
End Sub

The one I use the most: Resetting all filters in the workbook that the module is stored in:

Sub ResetFilters()
      Dim ws As Worksheet  
      Dim wb As Workbook  
      Dim listObj As ListObject  
       Set wb = ThisWorkbook  
       'Set wb = ActiveWorkbook
       'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need
           For Each ws In wb.Worksheets  
              If ws.FilterMode Then 
              ws.ShowAllData  
              Else   
              End If   
     'This removes "normal" filters in the workbook - however, it doesn't remove table filters           
       For Each listObj In ws.ListObjects 
            If listObj.ShowHeaders Then   
                 listObj.AutoFilter.ShowAllData 
                 listObj.Sort.SortFields.Clear    
            End If     
       Next listObj

            Next   
'And this removes table filters. You need both aspects to make it work.  
    End Sub

Answer 21

This will clear only if you have filter and does not cause any error when there arent any filter. If ActiveSheet.AutoFilterMode Then ActiveSheet.Columns("A").AutoFilter

Answer 22

I am using this approach for a multi table and range sheet as a unique way.

Sub RemoveFilters(Ws As Worksheet)
Dim LO As ListObject
On Error Resume Next
    Ws.ShowAllData
    For Each LO In Ws.ListObjects
        LO.ShowAutoFilter = True
        LO.AutoFilter.ShowAllData
    Next
    Ws.ShowAllData
End Sub

Answer 23

Wow. Logging in afterwards deleted all but a portion of the first line. My mistake. However, this will be terse.

For both tests Enter text in A1 and A5 of Sheet1 Filter for blanks only. Run either test Enter text in A5 Try to filter!

Sub SubsequentFilterFails()
   With Sheet1     'assumes code name is still Sheet1
      .ShowAllData  'assumes a filter has been applied
      .Range(.Cells(2, 1), .Cells(7, 1)).EntireRow.Delete
   End With
End Sub

Sub SubsequentFilterWorks()
   With Sheet1
      .Cells.AutoFilter
      .Range(.Cells(2, 1), .Cells(7, 1)).EntireRow.Delete
      .Cells.AutoFilter
   End With
End Sub

Thus, when filters are being cleared in order to clean the worksheet .Cells.AutoFilter will be used.

Answer 24

Loop AutoFilter columns, if column is activated(on) then reset a column filter, you may insert a new criteria after a loop. This code does not remove AutoFilter banner.

Dim iCol as Long
Dim ws as Worksheet
...
For iCol = 1 To ws.AutoFilter.Filters.count
  If ws.AutoFilter.Filters(iCol).On Then ws.AutoFilter.Range.AutoFilter Field:=iCol
Next
...
ws.AutoFilter.Range.AutoFilter Field:=4, Criteria1:="AABBCC"

Answer 25

I found this answer in a Microsoft webpage

It uses the AutoFilterMode as a boolean .

   If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilter

Answer 26

You must select range of the table first before using ActiveSheet.ShowAllData