CODEWITHSUNDEEP
excelvbapivotpivot-table
Niels Perfors
Niels Perfors

Reputation: 192

PivotItem.Visible seems always true

I have the following pivottable:

Rowlables: - Store - OrderID - Article

Columnlables: - status

Value Lables: - Quantity

Above the pivot, I have a slicer for StoreGroup. For this example, let's state that every store is either in group A or B and that i have set the slicer to show A only. 1 order does not exist in multiple stores.

In VBA, I want to loop through all the remaining orders. Here's my code:

Sub TEST()
  Dim pvt as PivotTable
  Set pvt = Sheets("Pivot").PivotTables("Orders")

  Dim pvf as PivotField
  Set pvf = pvt.PivotFields("OrderID")

  Dim pvi as PivotItem
  For Each pvi in pvf.PivotItems
    If pvi.Visible = True Then
      Debug.Print pvi.Value
    End if
  Next pvi
End Sub

When I run this code, all existing OrderID's are listed, even those with StoreGroup B.

My question: How do I know if the order is hidden?

Upvotes: 0

Views: 147

Answers (1)

Niels Perfors
Niels Perfors

Reputation: 192

Public Function ItemIsActive(pvtItem As PivotItem) As Boolean
    On Error GoTo ErrHandler
    ItemIsActive = Not pvtItem.DataRange.EntireRow.Hidden
Exit Function
ErrHandler:
    ItemIsActive = False
End Function

This code checks if the datarange row is hidden

Upvotes: 1

Related Questions