CODEWITHSUNDEEP
vbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

Excel 2013 VBA clear active filter

I need to clear any active filters from a sheet before running a certain 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 the error

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

I got that code from an answer to this question Excel 2013 VBA Clear All Filters macro

However that question doesn't explain how to ignore the line if no filters are active.

How do I ignore this line if there are no currently active filters applied?

EDIT

For example, all column headings have been auto filtered, so if my sheet is filtered by 'Female' for example I need to remove that filter before running the macro, however if no filters have been applied, just run the macro as normal

Upvotes: 7

Views: 69648

Answers (3)

Shadmage
Shadmage

Reputation: 91

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

** Possible duplicate of thread: Excel 2013 VBA Clear All Filters Macro

Upvotes: 2

Paul Kelly
Paul Kelly

Reputation: 985

Use FilterMode instead of AutoFilterMode. I have dealt with filters frequently and this code works fine.

If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

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

Upvotes: 15

user4039065
user4039065

Reputation:

I sincerely admire your desire to program for specific circumstances but I have to admit that the quickest way to accomplish this is with On Error Resume Next.

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

You shouldn't have to break something to check if it exists but in VBA that is occasionally the best recourse. Personally, I rank On Error Resume Next right up there with SendKeys as a programming method.

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

Upvotes: 8

Related Questions