This post is about a bit of code that answered somebody’s Stack Overflow question, was fun to write, and taught me a few more things about the pivot table object model, which is my favorite object model. So neat, so tidy, so logical – just like pivot tables themselves.
What, you ask, do I mean by Value Field Characteristics? I mean the page, row and column fields and items that the value field is summing, counting or otherwise valuing. So in the picture below the selected value field has three row items: one each for Continent, Country and State.
The Code
Here’s the code to print that same information to the Immediate window. Like the Excel tooltip in the picture it also lists the Value field name, e.g., Sum of Population, and its source field, e.g., Population. (This can be handy when you’ve modified the value field name to something like else, like “Residents.”):
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentField As Excel.PivotField
Dim i As Long
On Error Resume Next
Set pvtCell = ActiveCell.PivotCell
If Err.Number <> 0 Then
MsgBox "The cursor needs to be in a pivot table"
Exit Sub
End If
On Error GoTo 0
If pvtCell.PivotCellType <> xlPivotCellValue Then
MsgBox "The cursor needs to be in a Value field cell"
Exit Sub
End If
Set pvtTable = pvtCell.PivotTable
For Each pvtField In pvtTable.PageFields
i = 0
For Each pvtItem In pvtField.PivotItems
If pvtItem.Visible Then
i = i + 1
Debug.Print "PageField " & pvtField.Name & " - Pivot Item " & i & " is " & pvtItem.Name
End If
Next pvtItem
Next pvtField
Debug.Print "Value Field Name is " & pvtCell.PivotField.Name
Debug.Print "Value Field Source is " & pvtCell.PivotField.SourceName
For i = 1 To pvtCell.RowItems.Count
Set pvtParentField = pvtCell.RowItems(i).Parent
Debug.Print "Row Item " & i & " is " & pvtCell.RowItems(i).Name & ". It's parent Row Field is: " & pvtParentField.Name
Next i
For i = 1 To pvtCell.ColumnItems.Count
Set pvtParentField = pvtCell.ColumnItems(i).Parent
Debug.Print "Column Item " & i & " is " & pvtCell.ColumnItems(i).Name; ". It's parent Column Field is: " & pvtParentField.Name
Next i
End Sub
Before answering this question I didn’t know about the PivotCell.RowItems and PivotCell.ColumnItems properties. They’re pretty cool.
The person who posted this on Stack Overflow was looking to create an “actual” drilldown, which I think meant using the output to write a SQL query. You could modify the output of this routine to do so, as in this pseudocode:
SELECT * FROM qryContinentCountryState
WHERE pvtParentItem.Name = pvtCell.RowItems(i).Name
AND …
Alrighty then. Thanks for dropping by!
Hello there Doug,
I have a related follow up question to this post and one that you made back in 2012 with the same data set.
The question I have is about the code you wrote to hide Single-Item pivot totals. I’ve used this code on a sheet and it works, but with one unintended consequence that I hope you might be able to help with.
Suppose that Algeria or Benin or any of the single-line Countries had a State/Province listed. When the code is executed that single State/Province is suppressed. I’m not savvy enough to figure out which part of your code is causing this, or if this is the result of the comment you made about collapsing February in one place but not in another.
My specific issue from my job is this: I work for an apparel manufacturer, we have “Styles”, example “1001” (a t shirt for example) and items like “1001-BLK S, 1001-BLK M, 1001-BLK L” (items being the style/color/size combination), etc. Some Styles only have a single Item, e.g. 1001-BLK OS (one size). When I use the code on a pivot table it works exactly as it should on the “Style” field, but hides the value in the single Item field which is not good. I can replicate this with your sample file – If I fill in all of the blank provinces on the data tab, create a new pivot, and run the code to remove the redundant totals, the Provinces on the single-entry Countries become hidden.
Do you have any ideas on how to resolve this?
Any help would be greatly appreciated, thank you. And if you would prefer that I post to StackOverflow – no problem, let me know.
VvM
Hi Varius,
You’ve hit on (one of) the weak spots of this method. I mention this limitation in that post, along with another one. It didn’t occur to me until I wrote the post because the list I used it for only had blanks in the single-item rows.
So, sorry, I don’t have anything to offer. If you do find out a way please let me know.