CODEWITHSUNDEEP
excelpivot-tablevba
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

How to count the visible items in a specific field of PivotTable without looping?

How to count the visible items in a specific field of PivotTable without looping through all the items?

The code:

ActiveSheet.PivotTables(1).PivotFields("MyFieldName").PivotItems.Count

returns the total number of items, no matter if they are selected or not.

I am looking for something like: 

ActiveWorkbook.SlicerCaches(1).VisibleSlicerItems.Count

Update. I want to count how many items are selected in [Country] field of the picture below:

enter image description here

(I burglared the picture from: http://peltiertech.com/referencing-pivot-table-ranges-in-vba/ Hope, Jon won't mind it.)

Upvotes: 3

Views: 5385

Answers (2)

mooseman
mooseman

Reputation: 2015

I know this is an old question, but here is a simple solution

ActiveSheet.PivotTables(1).PivotFields("MyFieldName").VisibleItems.Count

I needed this for a report automation project.

Upvotes: 2

agustin
agustin

Reputation: 1351

This simple macro

Sub visible_PivotItemCount()

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("PivotTable1"), "1"). _
    Slicers.Add ActiveSheet, , "Slicer_1", "MySlicer", 1, 1, 1, 1

MsgBox (ActiveWorkbook.SlicerCaches(1).VisibleSlicerItems.Count)

ActiveWorkbook.SlicerCaches("Slicer_1").Delete

End Sub

counts the number of selected filters using the VisibleSlicerItems.Count property

Upvotes: 1

Related Questions