Richard has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Filter clear when no filter on returns error
I want to clear any filter on my spreadsheet at the start of a function.
The code for this is ActiveSheet.ShowAllData.
However, if there is nothing filtered (which there won't be when I first run this function) I get an error.
"Runtime error 1004. ShowAllData method of Worksheet class failed"
How do I check to see if there is any data already filtered, or is there another bit of code I can use to get round this?
RE: filter clear when no filter on returns error
Hi Richard
Thanks for your question.
As you say the code Activesheet.ShowAllData returns an error if there is no filtering.
One way to work around this is to turn the filtering on and off at the start. Similar to selecting Data, Filer twice.
Sub RemoveFilter()
Range("A1").Select
Selection.CurrentRegion.AutoFilter
Selection.CurrentRegion.AutoFilter
End Sub
This will unfilter a list starting at cell A1 whether or not it is filtered.
It does assume that the list already has filter buttons showing.
Hope that helps with your example.
Regards
Doug
STL
