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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This will work too   If ActiveSheet FilterMode Then cells AutoFilter

User · Answer

this works nice    If ActiveSheet AutoFilterMode Then Cells AutoFilter

User · Answer

If the sheet already has a filter on it then  Sub Macro1       Cells AutoFilter End Sub  will remove it

User · Answer

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

User · Answer

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   quot AABBCC quot

User · Answer

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

User · Answer

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

User · Answer

Try this   If ActiveSheet AutoFilterMode Then ActiveSheet ShowAllData

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Im using  filtermode if filter is on it returns true  Dim returnValue As Boolean     returnValue   worksheet1 FilterMode      if returnValue Then     worksheet1 ShowAllData     End If

User · Answer

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

User · Answer

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

User · Answer

I found this answer in a Microsoft webpage   It uses the AutoFilterMode as a boolean       If Worksheets  Sheet1   AutoFilterMode Then Selection AutoFilter

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Excel 2013 VBA Clear All Filters macro

Examples related to excel

Examples related to vba

Examples related to macos