| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting filtered items in PivotTable
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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting filtered items in PivotTable
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" wrote in message ... 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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting filtered items in PivotTable
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" wrote in message ... 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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting filtered items in PivotTable
Sorry Nic - slowing down in my old age
Your answer is here MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("Gende r").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" wrote in message ... 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" wrote in message ... 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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting filtered items in PivotTable
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("Gende r").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" wrote in message ... 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" wrote in message ... 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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting filtered items in PivotTable
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("Gende r").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" wrote in message ... 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" wrote in message ... 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("GENDE R").CurrentPage = "MALE" b) to count : MFDATE_Cnt = ActiveSheet.PivotTables("myPT").PivotFields("GENDE R").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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 - how to do PivotTable on filtered, visible data only? | Excel Discussion (Misc queries) | |||
| items filtered | Excel Discussion (Misc queries) | |||
| PivotTable: Difference from Row Items | Excel Discussion (Misc queries) | |||
| counting filtered items | Excel Worksheet Functions | |||
| pivotTable and filtered list | Excel Programming | |||
Linear Mode
