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
"Nicodemus" wrote:
> Great ! Sounds nice !
> Do you perhaps know how to use 2 or more filters then ?
> ie : (male) and (age 45)
> Thx, Nico
>
> "Dude Ranch" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:F9E36C7A-8825-401C-8C71-(E-Mail Removed)...
> > > Hi Dude Ranch,
> > >
> > > I tried your suggestion, but unfortunately it doesn't help...
> > > Thx anyway.
> > >
> > > "Dude Ranch" wrote:
> > >
> > >> 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" <(E-Mail Removed)> wrote in message
> > >> news:C77913E7-AC17-4B05-80FC-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >
> >
|
Similar Threads
