counting filtered items in PivotTable

[Nicodemus]

Hello all,
In VBA, I try to count the items left after filtering a PivotTable, but I
can't get it working !
Here is what I do :
a) to filter :
ActiveSheet.PivotTables("myPT").PivotFields("GENDER").CurrentPage = "MALE"

b) to count :
MFDATE_Cnt =
ActiveSheet.PivotTables("myPT").PivotFields("GENDER").PivotItems.Count

The counting should differ after selecting "MALE" or "FEMALE", but still
gives me the same value, which is actually the total of both genders.

Can someone help me on this ?
Thx in advance,
Nicodemus

 

[Dude Ranch]

Nic
I have run inot issues like this before it could be that the Cache is still
full
Hit the VBA help button and have a look at
MissingItemsLimit Property XlPivotTableMissingItems.
XlPivotTableMissingItems can be one of these XlPivotTableMissingItems
constants.
xlMissingItemsDefault The default number of unique items per
PivotField allowed.
xlMissingItemsMax The maximum number of unique items per PivotField
allowed (32,500).
xlMissingItemsNone No unique items per PivotField allowed (zero).

You should ensure that Missing items NONE is used

I hope this helps but it could be something else

 

[Nicodemus]

Hi Dude Ranch,

I tried your suggestion, but unfortunately it doesn't help...
Thx anyway.

 

[Dude Ranch]

Sorry Nic - slowing down in my old age
Your answer is here

MFDATE_Cnt =
ActiveSheet.PivotTables("myPT").PivotFields("Gender").PivotItems("Male").RecordCount

This lovely piece of work will recordcount the number of Pivotitems that are
"Male" regadless of the Page setting
Not what you want but if you are hard coding as below then it is the same
result.

Aloha
Jeff

 

[Nicodemus]

Great ! Sounds nice !
Do you perhaps know how to use 2 or more filters then ?
ie : (male) and (age 45)
Thx, Nico

 

[Nicodemus]

Hello there,

I'm using another solution to count the filtered items :
I count how rows are use in the PivotTable :
MFDATE_Cnt = Cells(Cells.Rows.Count, "A").End(xlUp).Row - 6
then I deduct 6 rows :
2 rows for Filters (Gender,Age)
1 blank row
2 header rows
1 total row

This is probably not the best solution ! If someone has a better idea, I'll
be glad to hear about it.
Cheers,
Nicodemus