Count Number of Selected PivotTable Filters

[JWilley88]

Good afternoon folks!

I have another tough question.

I have a PivotTable Report Filter that I want to Count the amount of selections in that filter preferably via VBA but a formula would work ok too.

So for a Person's Name, there's a filter under it for Dates and I'd like to be able to count the amount of Dates selected
Ex. I select Jan, Feb, Mar to filter the data by, the Count returns a value of 3


This is sort of what I want but it isn't working:

Sub DateCount()


MsgBox Worksheets("Sheet1").PivotTables("Name").PivotFields("Dates").PivotItems.Count


End Sub

 

[Domenic]

Try...

[color=darkblue]Sub[/color] DateCount()

    [color=darkblue]Dim[/color] oPivotItem [color=darkblue]As[/color] PivotItem
    [color=darkblue]Dim[/color] lCnt [color=darkblue]As[/color] [color=darkblue]Integer[/color]

    lCnt = 0
    [color=darkblue]For[/color] [color=darkblue]Each[/color] oPivotItem [color=darkblue]In[/color] Worksheets("Sheet1").PivotTables("Name").PivotFields("Dates").PivotItems
        [color=darkblue]If[/color] oPivotItem.Visible [color=darkblue]Then[/color]
            lCnt = lCnt + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] oPivotItem
    
    MsgBox lCnt

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[kristjanolafs]

Have you tried this?

Set it up as slicer and then use: (maybe you don't need to make it as a slicer.

=+CUBESETCOUNT(Slicer_Month)