ShowAllData method of Worksheet class failed

AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering).

I had the same issue and got it working with

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then  ActiveSheet.ShowAllDataEnd If

This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on.

The second catch Or ActiveSheet.FilterMode should catch advanced filters

The simple way to avoid this is not to use the worksheet method ShowAllData

Autofilter has the same ShowAllData method which doesn't throw an error when the filter is enabled but no filter is set

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

The error ShowAllData method of Worksheet class failed usually occurs when you try to remove an applied filter when there is not one applied.

I am not certain if you are trying to remove the whole AutoFilter, or just remove any applied filter, but there are different approaches for each.

To remove an applied filter but leave AutoFilter on:

If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then    ActiveSheet.ShowAllDataEnd If

The rationale behind the above code is to test that there is an AutoFilter or whether a filter has been applied (this will also remove advanced filters).

To completely remove the AutoFilter:

ActiveSheet.AutoFilterMode = False

In the above case, you are simply disabling the AutoFilter completely.