In my last post I talked about identifying pivot table’s Values field, if it had one. That function plays a part in this post, which is shows two functions for determining if a pivot field has visible subtotals. As with the last post, I didn’t find much about this on the web. I even asked my first Excel question on Stack Overflow. After a bunch of experimentation I came up with a function that seems to always work. And then, whaddaya know I came up with a better one. I use the second function in my improved per-item conditional formatting utility, which I will post about soon.
What Do I Mean by “Visible Subtotals?”
In the picture below the pivot table is set to show subtotals for every field. However subtotals are actually visible only for the Region field. There’s none for the Items field, which makes sense since Items is the rightmost field, and its subtotals would just be a repeat of the individual item values:
The VBA Subtotals Property Does Half the Job
The first thing you might try in VBA is checking the pivot fields Subtotals property. However below you can see that it returns True for both fields. The issue is the same as above: Subtotals are turned on but they don’t show for the rightmost field:
My First Attempt
So, I wrote some code that:
1. Checks if a field’s subtotals are turned on. If not, the function returns False.
2. Checks if any fields with the same orientation as the field we’re checking is a Values field
3. Tests if the field we’re checking is in the last position for its orientation (including the Values field). If not, then subtotals are on and the function returns True.
Dim i As Long
Dim SubtotalsOn As Boolean
Dim pvt As Excel.PivotTable
Dim ValueField As Excel.PivotField
Dim FieldPosition As Long
With pvtFieldToCheck
'Only row and column fields can show subtotals,
If Not (.Orientation <> xlColumnField Or .Orientation <> xlRowField) Then
GoTo exit_point
End If
Set pvt = .Parent
'Get the pivot tables ValuesField
Set ValueField = GetValuesField(pvt)
'The Value field is a column or row field,
'but won't have subtotals
If ValueField Is pvtFieldToCheck Then
GoTo exit_point
End If
'There are 12 possible types of Subtotals (at least XL 2003 on)
'If any of them are TRUE then Subtotals are on.
For i = LBound(.Subtotals) To UBound(.Subtotals)
If .Subtotals(i) = True Then
SubtotalsOn = True
Exit For
End If
Next i
'No need to proceed if they aren't even on
If Not SubtotalsOn Then
GoTo exit_point
End If
FieldPosition = .Position
'This is confusing, but
'if the Values field's position is greater than the field-to-check's position
'we want to ignore the Values field, as it won't affect the field-to_check's visibility
If Not ValueField Is Nothing Then
If ValueField.Orientation = .Orientation And ValueField.Position > FieldPosition Then
FieldPosition = FieldPosition + 1
End If
End If
'If the field-to-check isn't in the last position
'(taking into account the Values field)
'then it's Subtotals will be visible
If (.Orientation = xlColumnField And pvt.ColumnFields.Count > FieldPosition) Or _
(.Orientation = xlRowField And pvt.RowFields.Count > FieldPosition) Then
PivotFieldSubtotalsVisible_OLD = True
End If
End With
exit_point:
End Function
A Better Way – PivotCell to the Rescue
The above seems to work fine, but it’s got kind of a feel-your-way-in-the-dark aspect to it. I would much rather just have some code that examines the actual pivot table and figures out whether a given field is currently showing any subtotals. Happily, I have found a way to do this.
It’s based on the Range.PivotCell object and its PivofField and PivotCellType properties, all of which go back to Excel 2003, according to this MSDN page. They allow you to cycle through a pivot table’s cells checking for ones with a PivotCellType of xlPivotCellSubtotal (or xlPivotCellCustomSubtotal ) and, if so, checking what PivotField the subtotals belong to. I’ll discuss this some more after the VBA.
The Code
Dim pvt As Excel.PivotTable
Dim cell As Excel.Range
With pvtFieldToCheck
'Only row and column fields can show subtotals,
If Not (.Orientation = xlColumnField Or .Orientation = xlRowField) Then
GoTo exit_point
End If
Set pvt = .Parent
For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
If cell.PivotCell.PivotCellType = xlPivotCellSubtotal Or cell.PivotCell.PivotCellType = xlPivotCellCustomSubtotal Then
If cell.PivotCell.PivotField.Name = .Name Then
PivotFieldSubtotalsVisible = True
GoTo exit_point
End If
End If
Next cell
End With
exit_point:
End Function
How it Works
The code above actually only checks the pivot table’s ColumnRange and RowRange. These ranges are highligthed in the picture below. The code checks this area for cells with a PivotCellType of subtotal or custom subtotal. There are 10 PivotCellTypes, nine of which can be found in the ColumnRange or RowRange areas (the data area of the pivot table consists just of the xlPivotCellValue type.
The picture below highlights the cells with a PivotCellType of either xlPivotCellSubtotal or xlPivotCellCustomSubtotal. The custom subtotals are ones such as Min, Max and Average. These can be set in the field options menu. If the code finds a cell whose PivotCell.PivotCellType property is one of these two it then checks the cell’s PivotCell.PivotField object for a match with the field passed to the function.
I Like PivotCells
I’ve used the Range.PivotTable object quite a bit over the years. But it’s just recently that I’ve delved into the Range.PivotCell property. Hopefully I’ve given you some ideas for how you could use it to poke around in pivot tables.
Have you used the Range.PivotCell property? If so, leave a comment (I also love comments, especially the ones that add to my knowledge and don’t require me to do anything but say “thanks”).
A Word of caution about this method.
It seems that this VBA code only works with the old style of Pivot Tables or so called “Classic Style” layout.
Given that it is necesary to select it from the Pivot Table Options menu (right click on any table cell)
Regards
Hello Hernan,
You’re right. Thanks! In addition I immediately discovered another error in the line that relies on the Union of RowRange and ColumnRange. Obviously, if the pivot table lacks one of those, the routine errors out. Pretty lousy code!