Trouble Setting and Clearing Filters

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