Clearing Multiple Filters?

[Mel]

I have data with numerous col's that can be filtered on. I've also built
graphs to drive off of this filtered data along with a list on the graph
pulling in what data is being filtered on. If I go back to my data tab to
clear all filters (menu bar - data/filters/clear all filters) this will not
update my list that i pulled on my graphs. My question is, is there a macro
out there that i can use to clear my filters? Maybe linking it to a button?

 

[Wigi]

You could indeed use a commandbutton with this bit of code:


If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

Change Activesheet if the autofilter is not on the active sheet.

Below this code, you can have some code to update the other results / charts
/ sheets, whatever.

 

[Mel]

Thank you but I seem to be having issues making the "code" work. I'm not
very familiar with writing macros. This is what I have so far:

Dim r As Long, LastRow As Long
Sheets("SE Variance").Select
LastRow = Sheets("SE Variance").UsedRange.Rows(Sheets("SE
Variance").UsedRange.Rows.Count).Row
For r = LastRow To 2 Step -1
Rows(r).EntireRow.Hidden = False
Next r
Sheets("SE Variance").Select
End Sub

It seems to be working however it still does not reset my filter boxes back
to all. It is releasing my filters but not putting them back to 'all'. Do
you have any suggestions on how to link your "code" in with what I have
above? Any help would be greatly appreciated!!! Thank you!!!!

 

[Roger Govier]

Hi Mel

Change Wigi's suggestion to

Sub ShowAll()
If Sheets("SE Variance")..FilterMode Then
Sheets("SE Variance")..ShowAllData
End If
End Sub

 

[Mel]

Thank you! This does work however it gets my data to show everything but
does not go in and set the filters to "all". do you know of a way to set a
macro that will go in and set them to "all"?

 

[Roger Govier]

Hi Mel

Sorry, in altering the code to suit your specific requirement, I
accidentally repeated a "." on two occurrences

It should have read
Sub ShowAll()
If Sheets("SE Variance").FilterMode Then
Sheets("SE Variance").ShowAllData
End If
End Sub