Macro for Reseting Filters in Pivot tables

G

gameover

Active Member
Joined
Jan 12, 2009
Messages
292
Hi All,

I have a pivot table which consists 49 columns and more than 7000 rows. I have made some check boxes which hide 42 columns and can be seen only after clicking on check boxes. Due to so many columns, User commonly forget in which column he has put a filter, so I need a macro which can directly reset all the filters to original value "All" for hidden and unhidden columns both and does not effect the macros assigned to check boxes i.e. the hidden columns must remain hidden but if there is any filter in those hidden columns that should be reset to its original value i.e. "ALL".
I hope I have cleared my self but still if any one of you have some questions you can ask me.
Please answer my question ASAP. I'll be thankful to all of you.
Thanks in Advance!!!!
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Not tested:

Code: 
Sub ClearPFFilters()
 
Dim PF as PivotField
Dim PT as PivotTable
Dim WS as Worksheet
 
Set WS = Worksheets("MySheet")
Set PT = WS.PivotTables("MyPivot")
 
For Each PF in PT
      PF.ClearAllFilters
Next PF
 
End Sub
 
Upvote 0
I assume that's 2007 code since the ClearAllFilters method doesn't exist in 2003 or prior?
 
Upvote 0
That's a dangerous assumption to make! :biggrin:
 
Upvote 0
Hi,

Thanks for the reply, but I am using Excel 2003!! Please help me as I have to present this report in less than 24 hours!!!!:(
 
Upvote 0
Hi

maybe instead of

Code: 
For Each PF in PT
      PF.ClearAllFilters
Next PF

this (untested)

Code: 
For Each PF in PT
      PF.Visible=True
Next PF



Robert
 
Upvote 0
Hi Robert,

Its still not working an error message comes up "Subscript out of scope":(
What should I do now?
 
Upvote 0
It may be that as the element of the Pivot you are dealing with is a PivotItem rather than a PivotField, so, again based on Yard's 2007 code, try

Code: 
Sub ClearPFFilters()
 
Dim PI as PivotItem
Dim PT as PivotTable
Dim WS as Worksheet
 
Set WS = Worksheets("MySheet")
Set PT = WS.PivotTables("MyPivot")
 
For Each PI in PT
      PI.Visible = True
Next PI
 
End Sub

Robert
 
Upvote 0
Try this:
Code: 
Sub RemovePivotColumnFilters()
   Dim pt As PivotTable
   Dim pf As PivotField
   Dim pi As PivotItem
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   pt.ManualUpdate = True
   For Each pf In pt.ColumnFields
      For Each pi In pf.HiddenItems
         pi.visible = True
      Next pi
   Next pf
   pt.ManualUpdate = False
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
change cell color of header when pivot selected
Replies
0
Views
86
cogswel__cogs
C
T
  • Question
Clear FIlters of Pivot Table after Cell Value Changes
Replies
4
Views
247
Alex Blakenburg
A
Rainbow_P
  • Solved
Pivot table's Filters, to deselect items when "Not all items showing"
Replies
7
Views
189
RoryA
RoryA
T
  • Question
Ensure that the filter is reset once cell value changes
Replies
4
Views
203
thatfarooq
T
C
  • Solved
Filtering data in dynamic search box - Hiding Tables
Replies
4
Views
257
CorperateCiv
C

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top