| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing Multiple Filters?
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Clearing Multiple Filters?
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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mel" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Clearing Multiple Filters?
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!!!! "Wigi" wrote: 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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mel" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Clearing Multiple Filters?
Hi Mel
Change Wigi's suggestion to Sub ShowAll() If Sheets("SE Variance")..FilterMode Then Sheets("SE Variance")..ShowAllData End If End Sub -- Regards Roger Govier "Mel" wrote in message ... 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!!!! "Wigi" wrote: 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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mel" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Clearing Multiple Filters?
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" wrote: Hi Mel Change Wigi's suggestion to Sub ShowAll() If Sheets("SE Variance")..FilterMode Then Sheets("SE Variance")..ShowAllData End If End Sub -- Regards Roger Govier "Mel" wrote in message ... 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!!!! "Wigi" wrote: 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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mel" wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Clearing Multiple Filters?
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 -- Regards Roger Govier "Mel" wrote in message ... 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" wrote: Hi Mel Change Wigi's suggestion to Sub ShowAll() If Sheets("SE Variance")..FilterMode Then Sheets("SE Variance")..ShowAllData End If End Sub -- Regards Roger Govier "Mel" wrote in message ... 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!!!! "Wigi" wrote: 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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mel" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filters using multiple sheets | Excel Worksheet Functions | |||
| Multiple Column filters | Excel Discussion (Misc queries) | |||
| How do I add multiple filters | Excel Discussion (Misc queries) | |||
| Multiple Filters in one Sheet | Excel Worksheet Functions | |||
| Clearing multiple cells in 1 click? | Excel Worksheet Functions | |||
Linear Mode
