PivotItems - Count, VBA

Kles
Kles used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Most Valuable Expert 2011
Awarded 2010

Commented:
the last sentence was meant to read

Then you can do a *sum* of UniqueVendors in your pivot table.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try:
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables(1)
Rf = pt.PivotFields("VNDR_NAME").VisibleItems.Count
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
rorya,
I tried:  Rf = pt.PivotFields("VNDR_NAME").VisibleItems.Count with no sucess.  have any other ideas?
Most Valuable Expert 2011
Top Expert 2011
Commented:
Ah, I missed that you had filtered another field. I think you will have to simply loop through the items - something like:
Function getVisCount(pf As PivotField) As Long
    Dim pi As PivotItem
    Dim lngCounter As Long, lngRow As Long
    
    For Each pi In pf.PivotItems
        On Error Resume Next
        lngRow = pi.LabelRange.Row
        If lngRow <> 0 Then
            lngCounter = lngCounter + 1
            lngRow = 0
        End If
        On Error GoTo 0
    Next pi
    getVisCount = lngCounter
End Function

Open in new window

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial