Edit

PivotCell object (Excel)

  • Article

Represents a cell in a PivotTable report.

Remarks

Use the PivotCell property of the Range collection to return a PivotCell object.

After a PivotCell object is returned, you can use the ColumnItems or RowItems property to determine the PivotItems collection that corresponds to the items on the column or row axis that represents the selected number.

Example

After a PivotCell object is returned, you can use the PivotCellType property to determine what type of cell a particular range is.

The following example determines if cell A5 in the PivotTable is a data item and notifies the user. This example assumes that a PivotTable exists on the active worksheet, and that cell A5 is contained in the PivotTable. If cell A5 is not in the PivotTable, the example handles the run-time error.

Sub CheckPivotCellType() 
 
 On Error GoTo Not_In_PivotTable 
 
 ' Determine if cell A5 is a data item in the PivotTable. 
 If Application.Range("A5").PivotCell.PivotCellType = xlPivotCellValue Then 
 MsgBox "The PivotCell at A5 is a data item." 
 Else 
 MsgBox "The PivotCell at A5 is not a data item." 
 End If 
 Exit Sub 
 
Not_In_PivotTable: 
 MsgBox "The chosen cell is not in a PivotTable." 
 
End Sub

This example determines the column field that the data item of cell B5 is in. It then determines if the column field title matches "Inventory" and notifies the user. The example assumes that a PivotTable exists on the active worksheet, and that column B of the worksheet contains a column field of the PivotTable.

Sub CheckColumnItems() 
 
 ' Determine if there is a match between the item and column field. 
 If Application.Range("B5").PivotCell.ColumnItems.Item(1) = "Inventory" Then 
 MsgBox "Item in B5 is a member of the 'Inventory' column field." 
 Else 
 MsgBox "Item in B5 is not a member of the 'Inventory' column field." 
 End If 
 
End Sub

Methods

Properties

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.