Trying to count pivot items on screen

[andyzauk]

Hi,

I am trying to count the amount of items visible on screen for a row field in a pivot table. I am doing this:

CategoryAmount = ActiveSheet.PivotTables("YTDTrendPivot").RowFields("Cost Buckets").VisibleItems.Count

This, however, shows me the list of items available in the dropdown list for "Cost Buckets", but not the amount of items actually shown on screen. The table has a list of months down the columns and when I only select the first three months then some of the 13 Cost Buckets will disappear because there are no data rows for it.

I have searched and searched but can't find a way to count the items actually VISIBLE on the screen within the pivot table, not the amount of items shown when you select the "Cost Bucket" dropdown.

Appreciate anyone's help.
Andy.

 

[SkipVought]

Andy,

"This, however, shows me the list of items available in the dropdown list for "Cost Buckets", but not the amount of items actually shown on screen."

I am confused. The statement you posted does not list ANYTHING! It returns a COUNT.

The Visible property of the PivotItem object will be either True or False. But that has nothing to do whether it is on your screen (the visible area of what is displayed) That is a WINDOW property. The item is visible or not, regardless if it is in your line of sight or not.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

You are into very dodgy territory if you want to try and count what is visible on screen. There is no native functionality to do this so you have to reply on text size. Then there is an issue with no. of pixesl on the screen and the resolution that someone has their monitor at - oh yes and the zoom they have set the spreadhset to and where they have activated on the screen

In short, it is a pig and is generally asked about as a workaround for something else

Can you elucidate on the business requirement for this. there may be a better way...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Trying to GUESS at the requirement, it kinda seems to me like it might be a Green Screen mindset.
[tt]
Here's what I see on this page.
[Hit F8]
Now here's what I see on this page.
[/tt]
T'aint the same, if that's the case.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

sorry.

reply = rely
pixesl = pixels
spreadhset = spreadsheet

damn my fat fingers!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[andyzauk]

Hi. Sorry, I meant it returns the count of the items on the screen. I realise this question is a bit confusing - I tried to get it out in such a rush.

I have a dataset for a full year with which I build a pivot table. One of the fields is "Cost Buckets". There are 13 cost buckets altogether in the year. However, if I only select months 1-3 (for example) in the columns field only 5 of those cost buckets have numbers in them so only 5 cost buckets are displayed in the pivot table as:

001.08 002.08 003.08

Staff costs 52,914 86,816 139,730
Consultancy 3,780 3,780
Other 335 3,865 4,200
Training 1,415 1,415
T&E 172 172

I am trying to figure out how many items are on screen, not how many cost buckets there are in total in the entire dataset. My macro:

CategoryAmount = ActiveSheet.PivotTables("YTDTrendPivot").RowFields("Cost Buckets").VisibleItems.Count

returns 13, not 5.

Thanks for your help.
Andrew.

 

[SkipVought]

Please be precise. Your example does not support the description of your problem.

Do you need to ALSO select in the COLUMNS area?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[andyzauk]

I'm really not sure how I can make my problem any clearer, sorry! I really want to try and get it right, so I'll start from scratch.

I'm building a pivot table with a dataset with the following column headings:

Fiscal year/period
Cost Center
Cost Centre Name
Cost Element
Cost Element Name
Vendor Vendor Name
CO Doc Line Item Txt
FI Document Number
GBP Amount
USD Amt
Cost Buckets

I create the pivot table with "Fiscal year/Period" as my column field, and "Cost Buckets" as my row field, and "USD Amt" as my data field.

Within Cost Buckets there are 13 distinct items. Some of these occur in only certain months so when I filter "Fiscal Year/Period" to only months 1-3, only 5 of the 13 distinct cost buckets have data items in the pivot table.

What I'm TRYING to do is count the amount of cost buckets in months 1-3 [or whatever month(s) I pick]. However, the statement:

ActiveSheet.PivotTables("YTDTrendPivot").RowFields("Cost Buckets").VisibleItems.Count

returns all 13 distinct "Cost Buckets" items in the dataset (which is what you see when you dropdown the "Cost Buckets" selector list in the pivot table), not just the 5 that has data items (ie "USD Amounts") in months 1-3.

Is that possible?

Thanks again,
Andy.

 

[andyzauk]

Again, when I say it returns all 13 items I mean it returns the count! The number 13. I want it to return the number 5.

Andy.

 

[xlbo]

I get you. See this thread:

thread707-1426398

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[andyzauk]

Thanks, took a while but I got there.

Andy.