Thanks for your contribution Ryan. You just forced me into testing this
because for some reason I had the idea that AutoFilterMode and FilterMode
could not be used in the one line because I was sure that FilterMode produced
an error if the filter dropdowns were not visible. However, my belief was not
correct and I have now realized that the code can be simplified even more by
just testing for FilterMode.

Private Sub CommandButton1_Click() 'Clears all filters
With ActiveSheet
If .FilterMode Then .ShowAllData
End With
End Sub

Tested in xl2002 and xl2007 and it works fine.



--
Regards,

OssieMac


"Ryan H" wrote:

I figured there was a way to test if data is filtered or not. I hate using
the On Error Goto Next statement.

Private Sub CommandButton1_Click() 'Clears all filters
With Sheets("Your Sheet Name Here")
If .AutoFilterMode And .FilterMode Then .ShowAllData
End With
End Sub

--
Cheers,
Ryan


"OssieMac" wrote:

Hi oldjay,

This firstly tests if filtering dropdown arrows are on then tests if a
filter is actually applied.

Private Sub CommandButton1_Click() 'Clears all filters
With ActiveSheet
If .AutoFilterMode Then
If .FilterMode Then
ActiveSheet.ShowAllData
End If
End If
End With
End Sub

--
Regards,

OssieMac


"oldjay" wrote:

This fails at the last line "ShowAllData method of worksheet class failed" if
there is no filtering active

Private Sub CommandButton1_Click() 'Clears all filters
Range("A3").Select
ActiveSheet.ShowAllData
End Sub

End Sub