• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / PowerBI stuff / The Grand Totals range of a pivottable in Excel

The Grand Totals range of a pivottable in Excel

2020/10/19 by Frans

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.

Email *



Filed Under: Pivottable stuff, PowerBI stuff Tagged With: PivotTable, Power BI, VBA

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in