Pivot Table Pivot Item Property in Excel VBA (Visual Basic for Applications)

PivotItem

Is a property which refers to an item available top upper left in a PivotTable. It offers various properties and methods as listed below:

Data source

Methods and Properties

1. Caption: similar to Name property which returns Label text
2. Name: similar to Caption property which returns cell text
3. DataRange: is an object which offers Address property by which we can determine data range of the pivot table
4. Delete: is a method which deletes a pivot item
5. Formula: if cell contains formula the property will return formula else empty string
6. IsCalculated: if PivotItem is calculated then returns True else False
7. LabelRange: returns Range object which indicates available labels with PivotItems
8. RecordCount: it returns total number of records available in Pivot Cache
9. Visible: a boolean property to determine visible state of a PivotItem

Code example

Public Sub PivotItemVBAExample()
    
    'Declare range object
    Dim oRange As Range
    
    'Bind selection
    Set oRange = Selection
    
    'Declare pivot item
    Dim oPivotItem As PivotItem
    
    'bind pivot item
    Set oPivotItem = oRange.PivotItem
    
    'Print each Item name
    Debug.Print "Name : " & oPivotItem.Name
    
    'Print data range
    Debug.Print "Data Range : " & oPivotItem.DataRange.Address
    
    'Get label range
    Debug.Print "Label Range : " & oPivotItem.LabelRange.Address
    
    'Delete pivot item
    oPivotItem.Delete
    
    'Memory cleanup
    Set oRange = Nothing
    Set oPivotItem = Nothing
End Sub

Output

Name : Sum of Usage
Data Range : $F$3:$L$8
Label Range : $E$1

Next >> Pivot Cell object with code example Excel Pivot

Share on Facebook

Tweet