Forum Discussion

SpaceNorman's avatar
SpaceNorman
Copper Contributor
Dec 02, 2025

Trouble Setting and Clearing Filters

Hello all.   I'm on Day #2 of my VBA experience.    I'm working on a spreadsheet in which I'm using a series of command buttons to filter the content my data.  I have the following three subroutines - each associated with an individual command button.  (ok ... I lied, there's about 20 buttons - each of which sets a different filter - but I can demonstrate my issue with just these three)

When "Button A" is selected - it executes the following code:

Sub xJitFilter1()
    Rows("14:14").Select
    Selection.AutoFilter
    ActiveSheet.Range("JitComp1").AutoFilter Field:=13, Criteria1:="1"
End Sub

When "Button B" is selected - it executes the following code:

Sub xJitFilter2()
    Rows("14:14").Select
    Selection.AutoFilter
    ActiveSheet.Range("JitComp2").AutoFilter Field:=14, Criteria1:="1"
End Sub

When "Button C" is selected - it executes the following code:

Sub ClearAndReturn()
    Rows("14:14").Select
       If ActiveSheet.FilterMode Then
           ActiveSheet.ShowAllData
       End If
    Selection.AutoFilter
    Range("xJitLanding").Select
End Sub

It works just like I want it to - IF I select the buttons in a specific order.   For example:  

Button A  --> Button C --> Button B     Works perfectly

However, IF I select buttons in a different order (i.e.,)

Button B --> Button C        Crash and burn:    Run Time Error 1004:  Autofilter Method of Range Class failed

If I selection Button C (i.e., ClearAndReturn) when first opening the spreadsheet - prior to selecting one of the "Set Filter" buttons (i.e., Button A or Button B)  - it's crash and burn with the same Run Time Error.   

Bottom LIne:   IF I select any of my 20+ "Set Filter" buttons - and followed by the "ClearAndReturn" sub ... I can bounce between filters all day long.   The "ClearAndReturn" sub without having selected a "Set Filter" button or try to go from one "Set Filter" button to another ... instand fail. 

I assume I'm failing to reset something in the environment ... but darned if I know what!  Can anybody point me in the right direction?

Thanks - SpaceNorman

Macros and VBA

1 Reply

  • MrCharlesJenkins's avatar
    MrCharlesJenkins
    MCT
    Dec 02, 2025

    The error happens because ShowAllData (and sometimes AutoFilter) is being called when there is no active filter on the sheet or not on the same range, so Excel throws “AutoFilter method of Range class failed.” A simple fix is to always point your code at the same filtered range and only try to clear filters when they actually exist. For example, instead of using Rows("14:14").Select and Selection.AutoFilter, use a consistent range and remove the Select completely: With ActiveSheet.Range("JitComp1") : If .Parent.AutoFilterMode Then .AutoFilter : End If : End With (or whichever range is really your main filter area). In your ClearAndReturn macro, wrap the clear in a check like If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData and don’t call ShowAllData when no filter has been applied yet. The combination of (1) always using the same range for filters and (2) checking AutoFilterMode before clearing will stop the runtime 1004 when you press Clear first or switch directly from one filter button to another.

    ------------------------------------
    Don't forget to mark as solution if my answer suits you

Resources