The Grand Totals range of a pivottable in Excel • Pixcels.nl

In a recent project I needed the range of the Grand Totals in any pivottable in Excel. Unfortunately the PivotTable object has no property for this. Solutions I found on the Internet were for specific cases. So I developed a generic solution that I want to share with you.

Example

Let’s look at an example of a pivottable:

*Fig 1: A Pivottable’s Grand Totals range*

This pivottable (based on data from AdventureWorks) has the following definition:

*Fig 2: PivotTable Fields of the pivottable in Fig 1*

The pivottable has two Grand Totals on rows, because there are two measures in the Values area. Note that when we move the Values from the Columns to the Rows section we will have two Grand Totals on each column, like so:

*Fig 3: The same pivot with the Values on rows*

So, we can have one, two or even more Grand Total lines on rows or columns. Also, we can have no Grand Totals at all, when we set the properties PivotTable.RowGrand and PivotTable.ColumnGrand to false.

How to determine Grand Totals?

Table 1 gives an overview of all range properties that are available in the PivotTable object and it’s building blocks:

*Table 1: Range properties of the PivotTable*

(In this post Jon Peltier gave a useful visualization of these ranges.)

Unfortunately there are no ranges like GrandTotalsRange, RowGrandTotalsRange or ColumnGrandTotalsRange. So we need other properties to determine if a cell or range is a Grand Total.

On cell level we could use PivotCell.PivotCellType = xlPivotCellGrandTotal (value 3). However, this is more complicated as it seams, especially in the ColumnRange area. So I chose another strategy.

PivotLines

The PivotTable object has two properties PivotColumnAxis and PivotRowAxis of type PivotAxis, containing all lines on columns or rows of the pivot. A PivotAxis is a collection of PivotLines, each line is a set of PivotCell objects. However, we do not need the PivotCells, because the PivotLine has a useful property LineType. Possible values are:

xlPivotLineRegular (0): a regular PivotLine with pivot items.
xlPivotLineSubtotal (1): a Subtotal line.
xlPivotLineGrandTotal (2): a Grand Total line.
xlPivotLineBlank (3): a blank line after each group.

So, to determine the range of row Grand Totals (that is, the two columns on the right in Fig. 1), we first need to count the PivotLines of LineType 2. Next, we resize and offset the DataBodyRange of the pivot. This is the function:

Private Function GetRowGrandTotalsRange(myPivotTable As Excel.PivotTable) As Excel.Range
    Dim pl As Excel.PivotLine
    Dim TotalLinesCount As Long
    Dim i As Long
    Set GetRowGrandTotalsRange = Nothing 
    With myPivotTable
        TotalLinesCount = 0
        For i = .PivotColumnAxis.PivotLines.Count To 1 Step -1
            If .PivotColumnAxis.PivotLines(i).LineType = xlPivotLineGrandTotal Then
                TotalLinesCount = TotalLinesCount + 1
            Else
                Exit For
            End If
        Next
        If TotalLinesCount > 0 And .PivotRowAxis.PivotLines.Count > 0 Then
            With .DataBodyRange
                Set GetRowGrandTotalsRange = .Offset(, .Columns.Count - TotalLinesCount).Resize(, TotalLinesCount)
            End With
        End If
    End With
End Function

A few notes:

Perhaps a bit confusing, but to find the Totals on rows we have to inspect the lines on the PivotColumnAxis.
Line 8: basically the function loops though the pivotlines starting with the last one. The loop ends when the current pivotline has a wrong LineType, this can be the first one in the loop. Also it is possible that .PivotColumnAxis.PivotLines.Count = zero initially.
Line 15: under rare conditions it is possible to find one or more TotalLines while there are no lines on the RowAxis. In that case DataBodyRange returns an unexpected value.

Likewise there is a function GetColumnGrandTotalsRange, you find it in the attached workbook.

Bonus function: IsCellInGrandTotalsRange

In the attached workbook you also find a function IsCellInGrandTotalsRange, which uses the two functions described before.

Private Function IsCellInGrandTotalsRange(myCell As Excel.Range) As Boolean
    Dim rUnion As Excel.Range
    Dim rRowTotals As Excel.Range
    Dim rColumnTotals As Excel.Range
    Set rRowTotals = GetRowGrandTotalsRange(myCell.PivotTable)
    Set rColumnTotals = GetColumnGrandTotalsRange(myCell.PivotTable)
    If rRowTotals Is Nothing Then
        If rColumnTotals Is Nothing Then
            IsCellInGrandTotalsRange = False
        Else
            IsCellInGrandTotalsRange = Not (Application.Intersect(myCell, rColumnTotals) Is Nothing)
        End If
    Else
        If rColumnTotals Is Nothing Then
            IsCellInGrandTotalsRange = Not (Application.Intersect(myCell, rRowTotals) Is Nothing)
        Else
            Set rUnion = Application.Union(rRowTotals, rColumnTotals)
            IsCellInGrandTotalsRange = Not (Application.Intersect(myCell, rUnion) Is Nothing)
        End If
    End If
End Function

Link for download

Please note: the functions were only tested on Olap pivottables!

Enter your e-mail below and you will receive an email with a download link to the zip-file with GrandTotals.xlsm, containing a module with the above described functions, and a small Datamodel based on AdventureWorks you can use for test purposes.
We respect your privacy, and will use your e-mail only for communication related to this download.