asked on

PivotItems - Count, VBA

I am trying to get a count of the vendors in a pivot table. My problem is that ithe code below is giving me every Vendor in the PivotCache (6254). not the number shown on the pivot table (34).

I have mutiple pagefields selected in the PivotTable (below). VNDR_Name is in the rowfield.
Account#: = "59L"
Year: "2010"

Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables(1)
Rf = pt.PivotFields("VNDR_NAME").PivotItems.Count

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

not sure if you can do this with VBA. The common approach is to create the data in the source. For example, with your vendor name in column A, create a new column "UniqueVendors" in your data source with the formula

=IF(COUNTIF($A$1:A2,A2)>1,0,1)

(assuming data labels in row 1). Then you can do a count of UniqueVendors in your pivot table.

cheers, teylyn

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

the last sentence was meant to read

Then you can do a *sum* of UniqueVendors in your pivot table.

Rory Archibald

Try:
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables(1)
Rf = pt.PivotFields("VNDR_NAME").VisibleItems.Count

Kles

ASKER

rorya,
I tried: Rf = pt.PivotFields("VNDR_NAME").VisibleItems.Count with no sucess. have any other ideas?

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kles

ASKER

rorya,
That worked! terrific! I thought excel would have a property for the visible items of a pivot field in an pivot table. (if it does exist, i couldn't get it to work)
Thanks for the function! i never would have thought to count the lablerange rows.
Kles