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.
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode = True Then
Did I miss anything?
Thanks in advance for your help,
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
on error resume next
On Error goto 0
---------- FOLLOW-UP ----------
QUESTION: Thanks Tom. Yes, I am using a table to produce the autofilter. Would that be causing ActiveSheet.ShowAllData not to work?
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
Dim tbl As ListObject
For Each tbl In ActiveSheet.ListObjects
Possibly that will help you.
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