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.
Let’s look at an example of a pivottable:
This pivottable (based on data from AdventureWorks) has the following definition:
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:
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:
(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.
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.