Filter Reset Button

[Allan Sabado]

Hi Gurus,

Is there a way to set a single button in my Excel dashboard to click that will reset all my slicers/filters?

If so, how do I make it?

Again, Appreciate the help.

 

[Hui]

I Think a simple macro will do the job

Sub Show_All_Data()
  Worksheets("My sht").select 'Change to suit
  ActiveSheet.ShowAllData
End Sub

Copy the code into a VBA Module
Then link it to a custom button

 

[Allan Sabado]

Hi Hui,

Thanks however it is giving me ShowAllData method of Worksheet class failed?

 

[Deepak]

Hi Hui,

Thanks however it is giving me ShowAllData method of Worksheet class failed?

This error would occurred when there's no filter on the sheet.
You may try this too.

Sub Show_All_Data2()
Worksheets("My sht").Select 'Change to suit
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
End Sub

 

[Deepak]

Or Try this!!!

Sub Show_All_Data3()
Dim cache As SlicerCache
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.ShowAllData
For Each cache In ActiveWorkbook.SlicerCaches
 cache.ClearManualFilter
Next cache
'http://msdn.microsoft.com/en-us/library/office/ff195843%28v=office.14%29.aspx
End Sub

 

[Allan Sabado]

Thanks Deepak!