98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Filter clear when no filter on returns error | Excel forum
Filter clear when no filter on returns error | Excel forum
Resolved · Urgent Priority · Version 2016
Tue 27 Dec 2016, 14:38Delegate Richard said...
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?
Tue 3 Jan 2017, 10:31Trainer Doug said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Autofit column width – Excel (all versions)a. Highlight the column or columns you wish to alter the width of. You do this by clicking on the grey button at the top of the column showing the column letter. Click and drag on these letters to select more than one column. |
