Excel 2013 VBA clear active filter

Question

I need to clear any active filters from the sheet before running a specific macro, this line works just fine if there is an active filter on

If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 

However, if no filters are selected, it returns an error

 Runtime error '1004'; ShowAllData method of Worksheet class failed 

I got this code from the answer to this question Excel 2013 VBA Clear all filter filters

However, this question does not explain how to ignore a string if filters are not active.

How to ignore this line if no active filters are currently applied?

EDIT

For example, all column headings were automatically filtered, so if my sheet is filtered "Female", for example, I need to remove this filter before running the macro, however, if the filters were not applied, just run the macro as normal

Answer 1

Use FilterMode instead of AutoFilterMode. I often worked on filters, and this code works fine.

 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If 

Make sure the worksheet is not protected, as this also gives error 1004.

Answer 2

I sincerely admire your desire to program specific circumstances, but I must admit that the fastest way to accomplish this is with On Error Resume Next .

 On Error Resume Next ActiveSheet.ShowAllData On Error GoTo 0 

You do not need to break something to check if it exists, but in VBA, which is sometimes the best way to handle it. Personally, I value On Error Resume Next where SendKeys as a programming method.

The above method does not require you to check if .AutoFilterMode True.

Answer 3

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

Hope this helps someone :)

 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 

** Possible duplicate stream: Excel 2013 VBA Clear all macro filters