PDA

View Full Version : How to exclude hidden pivot field items in count



nocando
08-11-2009, 04:16 AM
Hi all,

I am new to this forum :hi: - and hope some of you experts can help me with this little challenge...

I need to count the number of visible items in a given pivotfield. I know I can set the pivot table up to do the count, but I don't want to alter the format of the current table.

The code below renders all items in the pivot field, but I want to exclude the hidden ones.




Sub Count_items()
Dim pt As PivotTable
Dim pf As PivotField

Set pt = Sheets("MASTER").Range("A2").PivotTable
Set pf = pt.PageFields("team")

MsgBox pf.PivotItems.count

End Sub


Any suggestions?

Bob Phillips
08-11-2009, 04:39 AM
Sub Count_items()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim cnt As Long

Set pt = Sheets("MASTER").Range("A2").PivotTable
Set pf = pt.PageFields("team")

For Each pi In pf.PivotItems

cnt = cnt - pi.Visible
Next pi
MsgBox cnt
End Sub

nocando
08-11-2009, 05:13 AM
Thanx for the quick reply!

This seems to give me the count of hidden items. So I presume I should change

cnt = cnt - pi.Visible


to something else in order to exclude the hidden items and not the visible ones. Or am I getting this all wrong?

Bob Phillips
08-11-2009, 05:48 AM
No, that is the count of visible items.