PivotItemList Members
Use the PivotItemList
collection to find the row and column of a PivotCell
. Use the PivotCell
object’s RowItems
and ColumnItems
properties to get a reference to this collection. The PivotItemList
collection has the following members:
Application Count Creator Item Parent
The following code displays the row and column of a selected cell in a pivot table’s data area:
Sub GetRowAndColumn( ) Dim pc As PivotCell, pi As PivotItem On Error Resume Next ' Get the pivot cell Set pc = Selection.PivotCell ' Show the row this item belongs to. For Each pi In pc.RowItems Debug.Print "Row: " & pi.Value Next ' Show the column this item belongs to. For Each pi In pc.ColumnItems Debug.Print "Column: " & pi.Value Next If Err Then Debug.Print "Selection is not in the data area." On Error GoTo 0 End Sub
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.