Class PivotLine (Excel VBA)

A PivotLine object is a line of rows or columns in an Excel PivotTable.


To use a PivotLine class variable it first needs to be instantiated, for example

Dim pvtln as PivotLine
Set pvtln = ActiveCell.PivotCell.PivotColumnLine

The following procedures can be used to set variables of type PivotLine: PivotCell.PivotColumnLine, PivotCell.PivotRowLine, PivotField.AutoSortPivotLine and PivotLines.Item.

For Each

Here is an example of processing the PivotLine items in a collection.

Dim pvtln As PivotLine
For Each pvtln In ActiveCell.PivotLines
Next pvtln


Returns an XlPivotLineType constant that indicates the type of PivotLine.

xlPivotLineBlank - Blank line after each group, xlPivotLineGrandTotal - Grand Total line, xlPivotLineRegular - Regular PivotLine with pivot items, xlPivotLineSubtotal - Subtotal line.
Dim xplLineType As XlPivotLineType
xplLineType = ActiveCell.PivotCell.PivotColumnLine.LineType


Returns a collection of PivotCell objects in a PivotLine.

Dim plcsPivotLineCells As PivotLineCells
Set plcsPivotLineCells = ActiveCell.PivotCell.PivotColumnLine.PivotLineCells


Retrieves all the PivotLine cells including those that are hidden in compact form.

Dim plcsPivotLineCellsFull As PivotLineCells
Set plcsPivotLineCellsFull = ActiveCell.PivotCell.PivotColumnLine.PivotLineCellsFull


Returns or sets the position of the PivotLine object.

Dim lngPosition As Long
lngPosition = ActiveCell.PivotCell.PivotColumnLine.Position


Returns the number of items in the PivotLines collection.

Dim lngCount As Long
lngCount = ActiveCell.PivotTable.PivotColumnAxis.PivotLines.Count


Returns a specific element of the PivotLines collection object by its position in the collection.

Item (Index)

Index: Specifies the position of an element of the collection.

Dim pvtlnItem As PivotLine
Set pvtlnItem = ActiveCell.PivotTable.PivotColumnAxis.PivotLines(Index:=1)