Filter clear when no filter on returns error | Excel forum

Resolved · Urgent Priority · Version 2016

Tue 27 Dec 2016, 14:38 Delegate 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:31 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

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.
b. Double click the dividing line between the columns. This dividing line is the break between the columns on the column headers (grey buttons showing the column letter at the top of each column). When you hover your mouse over one of these dividing lines the point will change and show an arrow pulling a line in two directions. When you have this mouse pointer you should double click to get Excel to automatically set the column width to fit the contents of the column (autofit)

View all Excel hints and tips