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 SubI 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.
126 Answers
Try this:
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllDataShowAllData will throw an error if a filter isn't currently applied. This will work:
Sub ResetFilters() On Error Resume Next ActiveSheet.ShowAllData
End SubIf the sheet already has a filter on it then:
Sub Macro1() Cells.AutoFilter
End Subwill remove it.
1For tables try this to check if it's on and turn off:
If wrkSheetCodeName.ListObjects("TableName").ShowAutoFilter Then wrkSheetCodeName.ListObjects("TableName").Range.AutoFilter
End ifTo Turn back on:
wrkSheetCodeName.ListObjects("TableName").Range.AutoFilterthis works nice.!
If ActiveSheet.AutoFilterMode Then Cells.AutoFilterThat 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 SubI 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 SubThere 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 SubThis 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.
5I 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 = TrueThis will work too:
If ActiveSheet.FilterMode Then cells.AutoFilterI usually use this code
Sub AutoFilter_Remove() Sheet1.AutoFilterMode = False 'Change Sheet1 to the relevant sheet 'Alternatively: Worksheets("[Your Sheet Name]").AutoFilterMode = False
End SubThis 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
NextTry 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.
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.AutoFilterAll you need is:
ActiveSheet.AutoFilter.ShowAllDataWhy? 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.
This works best for me.
I usually use the following before I save and close the files.
Sub remove_filters
ActiveSheet.AutofilterMode = False
End SubSimply activate the filter headers and run showalldata, works 100%. Something like:
Range("A1:Z1").Activate
ActiveSheet.ShowAllData
Range("R1:Y1").Activate
ActiveSheet.ShowAllDataIf you have the field headers in A1:Z1 and R1:Y1 respectively.
Im using .filtermode if filter is on it returns true
Dim returnValue As Boolean returnValue = worksheet1.FilterMode if returnValue Then worksheet1.ShowAllData End IfTry 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 SubThis Code clears all filters and removes sorting.
Source: Removing Filters for Each Table in a Workbook, VBA
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 SubThis 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 SubWe 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 SubYou 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 SubThe 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 SubThis 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
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 SubWow. 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 SubThus, when filters are being cleared in order to clean the worksheet .Cells.AutoFilter will be used.
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"I found this answer in a Microsoft webpage
It uses the AutoFilterMode as a boolean .
If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilterYou must select range of the table first before using ActiveSheet.ShowAllData
