VBA Count Visible Pivot Items

[CarolynS]

I have a pivot table that shows usernames and a count of how many times each username appears in my data. The pivot table also has a filter field which filters the data by course title.
I have set the filter so that the pivot table only displays data for 1 specific course. This gives a list of 25 usernames in the table. I would like to count the number of usernames using VBA. I have tried the following code but it gives a count of the total items (4000 odd) not just the visible ones (25)

mycount = WSID.PivotTables("Failure Rate Analysis").PivotFields("Person - Username").PivotItems.Count

I've tried .visibleitems.count but this doesn't work either.

Have been searching for a solution online for hours but can't find one that works.

Once I've counted the total visible usernames for the course i need to count how many of these usernames have a count of 2-5 in the total field of the table

i.e. there might be 2 usernames with a count of 2, 1 with a count of 3, 2 with a count of 5 so I would want to return a toal of 5

Thanks

 

[Worf]

I made up a pivot table and this code worked.
Can you post a link to the workbook or does it contain sensitive data?

Sub CountVisible()
Dim ws As Worksheet, pt As PivotTable, pf As PivotField
Set ws = ActiveSheet
Set pt = ws.PivotTables("pivottable1")
Set pf = pt.PivotFields("group")
MsgBox "Total: " & pf.PivotItems.Count & vbLf & "Visible: " & pf.VisibleItems.Count
End Sub