Excel/Remove AutoFilter

Advertisement


Question
QUESTION: Hi Tom,

Have a question about autofilter and hope you could help with.

I have a spreadsheet that contains one tab, which has 4 autofilter macros. I can see the data by clicking on each autofilter macro.

I am trying to add the clear filter button with the following, but it does not clear off the autofilter.

Sub Clear_Filters()
If ActiveSheet.AutoFilterMode Then
   If ActiveSheet.FilterMode = True Then
       ActiveSheet.ShowAllData
   End If
End If
End Sub

Did I miss anything?

Thanks in advance for your help,

Tim

ANSWER: Tim,

You check if the sheet has autofilter dropdown arrows showing.

then you check if there are rows that are actually hidden due to the autofilter having a condition applied.

If these are both met than you showalldata.  

that seems correct to me.

Only concern I might have is if you are using a Listobject/table to produce the filter.  But if you are using an autofilter on the sheet I would expect your code to work.  You could just do

Sub Clear_Filters()
on error resume next
Activesheet.ShowAllData
On Error goto 0
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thanks Tom. Yes, I am using a table to produce the autofilter. Would that be causing ActiveSheet.ShowAllData not to work?

Thanks,
Tim

Answer
Tim

That would be my guess - but you really didn't elaborate on what you meant by not work.

I had a worksheet with 4 tables (as introduced in Excel 2007 (the were listobjects in excel 2003 when introduced).  I used this to clear the filters

Sub abc()
Dim tbl As ListObject
For Each tbl In ActiveSheet.ListObjects
tbl.AutoFilter.ShowAllData
Next
End Sub


Possibly that will help you.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.