Pivot Tables Archives - yoursumbuddy

Locating PivotItem Subtotals

Posted on March 1, 2017 by Doug Glancy

I’m either on a roll or in a rut: here’s one more post about pivot field stuff. Last time I posted about determining whether a give pivot field has visible subtotals. This time I’ll tell you how to find them. The solution again relies on my new friend, the PivotCell object. My main function actually locates PivotItem subtotals, not a PivotField’s. I then wrap that function in another routine to deal with all of a PivotField’s PivotItems.

Here’s the VBA:

Function GetPivotItemSubtotalRanges(pvtItem As Excel.PivotItem) As Excel.Range()
Dim pvt As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim cell As Excel.Range
Dim ItemTester As Excel.PivotItem
Dim PivotItemSubtotalRanges() As Excel.Range

If Not pvtItem.Visible Then
Exit Function
End If

'I can't figure a better way to get the containing pivot table
Set pvt = pvtItem.DataRange.Cells(1).PivotTable
Set pvtField = pvtItem.Parent
'Cells with subtotal PivotCellType are in ColumnRange or RowRange
For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
Set ItemTester = Nothing
On Error Resume Next
'Only test cells with an associated PivotItem
Set ItemTester = cell.PivotItem
On Error GoTo 0
With cell.PivotCell
If Not ItemTester Is Nothing Then
If (.PivotCellType = xlPivotCellSubtotal Or .PivotCellType = xlPivotCellCustomSubtotal) And cell.PivotField.DataRange.Address = pvtField.DataRange.Address And cell.PivotItem.DataRange.Address = pvtItem.DataRange.Address Then
RedimRanges PivotItemSubtotalRanges
If pvtField.Orientation = xlColumnField Then
Set PivotItemSubtotalRanges(UBound(PivotItemSubtotalRanges)) = Intersect(cell.EntireColumn, pvt.DataBodyRange)
ElseIf pvtField.Orientation = xlRowField Then
Set PivotItemSubtotalRanges(UBound(PivotItemSubtotalRanges)) = Intersect(cell.EntireRow, pvt.DataBodyRange)
End If
End If
End If
End With
Next cell

GetPivotItemSubtotalRanges = PivotItemSubtotalRanges
End Function

How It Works

Be sure to read the previous post on this topic for background of how I got here.

With that background in hand, what the function above does is fairly simple. It loops through the RowRange and ColumnRange of a PivotItem’s pivot table. It looks for cells with a Range.PivotItem property that matches the PivotItem passed to the function, and which have a Range.PivotCellType of Subtotal or CustomSubtotal. If so then that PivotItem subtotal range is set to the intersection of the pivot table’s DataBodyRange and the row or column of the cell being tested. The subtotal range is added to the array of subtotal ranges returned by the function.

The PivotField Routine

Here’s an example of using the GetPivotItemSubtotalRanges function. This Sub takes a PivotField as its argument and selects all of it’s subtotals.

Sub SelectPivotFieldSubtotals(pvtField As Excel.PivotField)
Dim pvtItem As Excel.PivotItem
Dim PivotItemSubtotalRanges() As Excel.Range
Dim PivotFieldSubtotals As Excel.Range
Dim i As Long

If Not PivotFieldSubtotalsVisible(pvtField) Then
MsgBox "No Visible Subtotals"
GoTo exit_point
End If
For Each pvtItem In pvtField.PivotItems
If pvtItem.RecordCount > 0 Then
PivotItemSubtotalRanges = GetPivotItemSubtotalRanges(pvtItem)
For i = LBound(PivotItemSubtotalRanges) To UBound(PivotItemSubtotalRanges)
If PivotFieldSubtotals Is Nothing Then
Set PivotFieldSubtotals = PivotItemSubtotalRanges(i)
Else
Set PivotFieldSubtotals = Union(PivotFieldSubtotals, PivotItemSubtotalRanges(i))
End If
Next i
End If
Next pvtItem
If i > 0 Then
PivotFieldSubtotals.Select
End If

exit_point:
End Sub

Stray Code Bits You’ll Need to Run the Above

This is the function that checks whether a PivotField has visible subtotals, and that I posted about previously:

Function PivotFieldSubtotalsVisible(pvtFieldToCheck As Excel.PivotField) As Boolean
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

This one is because I want to hide the fact that I’m Redimming a lot:

Sub RedimRanges(ByRef SubtotalDataRanges() As Excel.Range)
If IsArrayEmpty(SubtotalDataRanges) Then
ReDim SubtotalDataRanges(1 To 1)
Else
ReDim Preserve SubtotalDataRanges(LBound(SubtotalDataRanges) To UBound(SubtotalDataRanges) + 1)
End If
End Sub

This is Chip Pearson’s array check:

Public Function IsArrayEmpty(Arr As Variant) As Boolean
'Chip Pearson
Dim LB As Long
Dim UB As Long

Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
' we weren't passed an array, return True
IsArrayEmpty = True
End If
UB = UBound(Arr, 1)
If (Err.Number <> 0) Then
IsArrayEmpty = True
Else
Err.Clear
LB = LBound(Arr)
If LB > UB Then
IsArrayEmpty = True
Else
IsArrayEmpty = False
End If
End If
End Function

And this is what I attached to a button. Select a cell in a pivot table and if that cell’s PivotField has subtotals they will be highlighted:

Sub test()
SelectPivotFieldSubtotals ActiveCell.PivotField
End Sub

Subtotals Selected

In Conclusion

Whew! That feels like a lot of code with maybe not enough explanation. I plan to wrap up all this pivot field selection stuff soon with a post about my new-and-improved Per-Item Conditional Formatting tool.

Determining if a Pivot Field Has Visible Subtotals

Posted on January 24, 2017 by Doug Glancy

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:

All Subtotals at Bottom

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:

Subtotals in Immediate Window

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.

Function PivotFieldSubtotalsVisible_OLD(pvtFieldToCheck As Excel.PivotField) As Boolean
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

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.

ColumnRange and RowRange

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.

PivotCell Subtotal types

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”).

PivotItem.DataRange Off By One Row Bug

Posted on January 10, 2017 by Doug Glancy

This week I ran into a pivot table VBA issue I’ve never noticed before. When a pivot table has more than one data field, referring to a PivotItem.DataRange returns a range one row down from where it should be. Below you can see that the PivotItem.DataRange address is one row off and that the selection is below the pivot table:

PivotIItem.DataRange Offby One

If the pivot table has only one data field, e.g., if I get rid of “Sum of Total” above, the issue goes away.

I found one reference to this by Macro Marc on SO, but nothing else on the web. It seems like it would be a well-known thing though, especially if it’s been around for a while.

I’m curious if anybody knows whether this has been reported as a bug. I noticed it on my home computer running Office 365 Pro Plus. I’d be interested to hear if it’s on other versions.

My Workaround

In my very limited testing it seems like there isn’t a similar issue for PivotFields. So one idea is to compare the first row of a pivot field against the first row of its first pivot item and use the difference, if any, to offset the PivotItem.DataRange back to where it should be. However, I’m not sure that my concept of “first” will always be the same as Excel’s. Anyways I’m using this function:

Function GetPivotItemOffsetBugCorrection(pvt As Excel.PivotTable) As Long
'Only occurs if the pivot table has more than one data field
If pvt.DataFields.Count = 1 Then
Exit Function
End If

GetPivotItemOffsetBugCorrection = pvt.VisibleFields(1).DataRange.Row - _
pvt.VisibleFields(1).VisibleItems(1).DataRange.Row
End Function

Then I use it like this in places where I refer to a pivot item’s data range:

Set pvt = pvtItem.Parent.Parent
PivotItemOffsetBugCorrection = GetPivotItemOffsetBugCorrection(pvt)
For Each cell In pvtItem.DataRange.Offset(PivotItemOffsetBugCorrection)

Yuck!

If you’ve got a good solution for dealing with this, or any info, please leave a comment.

Identify a Pivot Table’s Values Field

Posted on January 5, 2017 by Doug Glancy

Over the next few posts I plan to delve into a couple of functions I’ve written to identify areas in a pivot table. I also want to do a quick post on a pivot quirk I noticed recently. I then plan to roll it up into a post on my new-and-improved per-pivot-item conditional formatting tool. It’s good to have plans, right? Anyways, let’s get started with a function to identify a pivot table’s Values field.

I deduced the following just by messing around – I couldn’t find anything on the web about identifying a Values field. If I got something wrong, or if you have a better way to do this, please leave a comment.

What is a Values Field?

The Values field is the one that appears when you have more than one data field. Its location in the Rows or Columns area of the pivot table dialogs controls the grouping of those data fields. In the following example, I’ve grouped the data area by data fields within years. In other words, the two summing data fields appear side-by-side for each year:

Values Field by years then values

In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:

Values Field by values then years

Some pivot table layouts, such as the one below, don’t show the word “Values” anywhere in the pivot table, but it still shows in the pivot table dialog:

Values Field Column Labels

Like all pivot fields, the Values field can be renamed. Note that though I changed it to “Frodo” in the pivot table, it still says “Values” in the dialog:

Values Field by values called Frodo

Everything I’ve said about the Columns area of the pivot dialog applies to the Rows area. The Values field behaves the same way there.

Identifying the Values Field in VBA

EDIT:

In the comments below Petra identified a much faster way using PivotTable.DataPivotField. DataPivotField contains the Values field, whether or not it’s visible. So,

If DataPivotField.Orientation <> 0

tests whether the Values field is present.

So, anyways, I wanted a VBA function that returns a pivot table’s Values field if it has one. When figuring out how to do this I asked myself:

Is the Values field a PivotTable.DataField or a PivotTable.ColumnField/RowField?

The answer is both, kind of. So, for instance, in the examples pictured above typing the following into the immediate window returns “Values”:

? ActiveCell.PivotTable.ColumnFields("Values").Name

And so does this:

? ActiveCell.PivotTable.DataFields("Values").Name

So it looks like the Values field is both a data and column (or row) field. To further confirm this, note that this statement returns True:

? ActiveCell.PivotTable.DataFields("Values").Orientation = xlColumnField

So, even though it’s both a Data and Column (or Row) field it looks like it’s a bit more of a Column field (I’m going to stop saying “or Row” now). This is backed up by the fact that you can’t refer to it’s Data personality using an index. In other words, the following returns an error:

? ActiveCell.PivotTable.DataFields(3).Name

(1 and 2 return the two other data fields)

Furthermore, if you check the

DataFields.Count

for the example above the count is only two.

Cutting to the Chase

In addition to the above, I’ve got one more informational tidbit: if you change the name of the Values field to “Frodo,” both its Data and Column selves refer to themselves as “Frodo.” So even though, as we’ve seen above, the dialog box continues to use the word “Values” to refer to this field,

? ActiveCell.PivotTable.DataFields("Values").Name

gets you a runtime error 1004.

This means that you can’t just refer to the values field using “Values” in either its DataField or ColumnField version. If you do and a user changes its name you’re out of luck.

Fortunately, this has an upside, and it’s not just that I have something to blog about. It means that a Values field name is the only field name in the pivot table that can be repeated for a Data field and a Column field. Usually two fields can’t have the same name. For example, in the examples above if you try to rename “Year” or “Values” to “Sum of Unit Cost” you’ll get a “Field name already exists” error. But in the case of a Values field both its Data and Columm/Row references will be the same name.

This means you can identify a pivot table’s Value field by finding a row or column field that has the same name as a data field. Cool, eh?

The Function

Function GetValueField(pvt As Excel.PivotTable) As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim TestField As Excel.PivotField
Dim ValueField As Excel.PivotField

'If there's only one data field then there won't be a Values field
If pvt.DataFields.Count = 1 Then
GoTo exit_point
End If

For Each pvtField In pvt.PivotFields
On Error Resume Next
'test each non-data field for a data field with a matching name
Set TestField = pvt.DataFields(pvtField.Name)
On Error GoTo 0
If Not TestField Is Nothing Then
'if there's a match then you've got the Values field
Set ValueField = pvtField
Exit For
End If
Next pvtField
Set GetValueField = ValueField

exit_point:
End Function

Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.

Pivot Table Pivot Chart Navigator

Posted on July 16, 2016 by Doug Glancy

This post is about navigating between pivot tables and pivot charts. The sample workbook contains a Pivot Table and Pivot Chart Navigator userform that lists the workbook’s pivot tables and takes you to them or their associated charts. The workbook also adds buttons to the chart and pivot table right-click menus. These buttons take you to the associated pivot chart or table. I used Ribbon XML for this last part since later versions of Excel don’t allow modification of the chart context menus with VBA. The downloadable workbook can be easily converted to an addin.

pivot chart context menu

I used to eschew pivot charts as far too clunky. Recently though I was given a project that contained many pivot charts. It seemed that, unless I’d just gotten much less picky (not likely), pivot charts work much better than I remembered. This impression was confirmed in a Jon Peltier post, so I know it’s true.

Using XML to Add to Right-Click Menus

As mentioned above, I’ve added a “Go to Source Pivot” button at the bottom of the chart context menu. I’d never used Ribbon XML to make a right-click menu before. The XML part is straightforward.

To create the button I used the Custom UI Editor and added a ContextMenu section to the XML. I also used the Microsoft’s NameX addin to figure out the name that refers to the chart context menu (ContextMenuChartArea) The XML for the chart and pivot table context menus is below. All of this, including links to the Custom UI Editor and the NameX addin, is covered very nicely in this MSDN post.

Since I’m already forced to use XML to modify the chart context menu, I used it for the pivot table context menu too, even though it can still be modified with VBA:

VBA to Go To Source Pivot
The code to go to the source pivot is similar to that in my Finding a Pivot Chart’s Pivot Table post. It looks at the charts PivotLayout property, which only exists if a chart is based on a pivot table. I use this same property in the RibbonInvalidate method to only show the “Go To Pivot Table” button when the chart is a pivot chart. That’s one thing I like about programming the ribbon: the code to show or hide tabs, buttons and other controls is generally simpler than it is when using VBA.

VBA to Go To Pivot Chart
The code to go to a pivot table’s chart loops through all chart sheets and charts on worksheets looking for one whose source range is the pivot table’s range:

Function GetPivotChart(pvt As Excel.PivotTable) As Excel.Chart
Dim wbWithPivots As Excel.Workbook
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart

Set wbWithPivots = pvt.Parent.Parent
For Each cht In wbWithPivots.Charts
If Not cht.PivotLayout Is Nothing Then
If cht.PivotLayout.PivotTable.TableRange1.Address(external:=True) = pvt.TableRange1.Address(external:=True) Then
Set GetPivotChart = cht
Exit Function
End If
End If
Next cht
For Each ws In wbWithPivots.Worksheets
For Each chtObject In ws.ChartObjects
Set cht = chtObject.Chart
If Not cht.PivotLayout Is Nothing Then
If cht.PivotLayout.PivotTable.TableRange1.Address(external:=True) = pvt.TableRange1.Address(external:=True) Then
Set GetPivotChart = cht
Exit Function
End If
End If
Next chtObject
Next ws
End Function

PivotNavigator Form
The other element of the sample workbook is a simple-yet-powerful form that navigates through a workbook’s pivot tables and pivot charts.

pivot navigator form

The form opens up with a list of all the pivot tables in the active workbook. Selecting an item in the form list takes you to the selected pivot. Use the Ctrl key with the left and right arrows to toggle between a pivot and its associated chart.

The form is modeless and responds to selection changes in the workbook, updating the list selection when you click into a different pivot or chart. This functionality uses VBA from my last post, which raises an event every time any chart in a workbook is selected.

Download
The sample workbook has the modified right-click menus, the navigation form and a button in the Developer tab to start the form. There’s even instructions!

Filtering Pivot Table Value Fields

Posted on May 21, 2016 by Doug Glancy

In the old days, before I knew better, when I needed to filter a pivot table Value field I’d do it by using by throwing an autofilter from the Data menu on it:

Pivot with Autofilter

It made me feel dirty but I didn’t know any other way to filter pivot table value fields. Clearly pivot tables provided no filter in the Values columns:

Pivot with no filter

And then one day I read this SuperUser answer by mtone and never looked back. If you don’t know how to apply a Value Filter to a non-Value pivot field I recommend reading it right now:

SuperUser Value Filter answer

Good wasn’t it? I started to write a post about this and realized this was the best explanation I’ve seen. I especially like that mtone explains how choosing different fields to apply the filter to will result in different levels of aggregation, and will change your results.

One Value Field Filtering Oddity

I was prompted to write this post after answering Bijan’s question on Stack Overflow. I quickly helped solve his problem but was perplexed because his original issue was that he was applying a value filter to the value field itself. As I said at the beginning, this isn’t possible. Turns out that’s not quite true. After an extended chat he showed me how he did it.

Before you can do this, you first need to use the field as a Row, Column or Report field. Simply dragging it to the Row area of the Show Filter dialog and then to the Values area will do the trick. Once you do so, you can click the down arrow next to the field name in the pivot’s Show Fields dialog:

show fields with dropdown

At that point you can pick the various Value filters.

value field Value Filters

As Bijan discovered though this doesn’t actually do anything. No filtering occurs (and if it did, it wouldn’t be an aggregate filter anyways). And when you drag the field back to a non-value position it, the filter that is, goes away. Weird and bug-like.

Getting Pivot Table Value Field Characteristics

Posted on February 27, 2016 by Doug Glancy

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.

value field

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.”):

Sub GetValueFieldStuff()
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!

Finding a Pivot Chart’s Pivot Table

Posted on February 12, 2016 by Doug Glancy

I don’t work with pivot charts very much, but recently I got the job of modifying a dashboard that uses a mess of them. As part of the modification, I’m deleting some of the charts, which is leaving behind unneeded pivot tables – pivot tables with unhelpful names like “PivotTable17.” In order to work with all this I wrote a quick routine for finding a pivot chart’s pivot table.

Until today I was under the impression you could create a pivot chart without having a pivot table. I thought they were just a different kind of representation of what’s in a pivot cache and that, as is true with pivot tables, you could have several feeding directly off one pivot cache. That’s not true. Every pivot chart requires a separate pivot table.

This meant that when I started deleting some pivot charts from the workbook I wanted to reduce the workbook clutter by also deleting their pivot tables. The trick was to find them. At first I tried clicking the ribbon’s “Change Data Source” button, but that points at the data the pivot table/chart combo is based on, not at the pivot table the chart is based on. I found I could see the pivot table name and the sheet it’s on in the pivot chart’s “Select Data” dialog. But then I was still stuck trying to figure out which pivot was which. What a perfect excuse to write some VBA and poke into some previously unexplored object model crannies!

The code below cycles through each pivot chart in a workbook, selects its pivot table and displays a msgbox with info about the pivot table. (In actual practice I collected all the pivot table data and used that to delete any pivot tables that weren’t in the collection. No hand-deleting if I can help it!)

The Code

Sub GetPivotChartSources()
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart
Dim pvt As Excel.PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each chtObject In ws.ChartObjects
Set cht = chtObject.Chart
If Not cht.PivotLayout Is Nothing Then
Set pvt = cht.PivotLayout.PivotTable
'activate the sheet the pivot is on
pvt.Parent.Activate
pvt.TableRange2.Cells(1).Select
MsgBox pvt.Name & " is on " & pvt.Parent.Name & " using data from " & pvt.SourceData
End If
Next chtObject
Next ws
End Sub

ChartObjects, PivotLayouts and More

The code above cycles through each ChartObject, which is the container for a chart embedded in a worksheet, as opposed to being its own tab. What we’re really interested in is the ChartObject’s chart object (ha!) so I set a variable to that. Then the key is that pivot charts have a PivotLayout* object, which in turn contains the pivot table object. Once you’ve got that you can access all the usual pivot table properties like Name, SourceData and TableRange2, which is the range containing the entire pivot table including the page filters.

After getting rid of the unneeded pivot tables I went back and ran very similar code to rename them to something more meaningful. First I renamed the charts from “Chart 17”, etc., to something like “chtRegionalRetentionRate” and then substituted this line into the heart of the code above:

pvt.Name = Replace(chtObject.Name, "cht", "pvt")

When I looked around the web for code to do this kind of stuff I didn’t find anything, and had to discover the crucial PivotLayout object on my own. So, as we used to say in the newsgroups, “hth”.

MSDN Errata?
* It seems to me that this MSDN PivotLayout page is wrong, and that it treats the PivotLayout object like the PivotLayout.PivotTable object.

Selection.ListObject and Selection.PivotTable

Posted on October 4, 2015 by Doug Glancy

In recent posts about using VBA to work with filters I’ve used ActiveCell.ListObject to test whether I’m dealing with a filtered table or a filtered range. At one point it occurred that maybe I should be looking at the Selection’s ListObject instead. For various reasons that doesn’t work, but it made me wonder how Selection.ListObject gets evaluated. For example, what if it spans two tables? And, while we’re at it what about Selection.PivotTable?

To be clear, everything said about Selection in this post actually applies to the more general Range object. It’s just that Selection is the range I’m interested in.

Selection.PivotTable

Let’s start with Pivot Tables. Excel’s help actually says exactly how Range.PivotTable is handled:

Returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the [Selection].

So,

Selection.PivotTable

returns an error with the selection below because its upper-left corner is outside the pivot:

But this selection will return the pivot table because the upper-left cell of the selection intersects the pivot table:

Selection.ListObject

The rules for ListObjects, or at least what I’m guessing are the rules, are different. There’s nothing I can find in Help, so here’s what I made up:

Returns the ListObject intersected by the Selection. If more than one ListObject intersects the Selection, the ListObject that was created earliest is returned.

None of the pivot table’s “upper-left corner” stuff. If the selection intersects the table, the table is returned. So below

Selection.Listobject

returns Table3.

When the Selection intersects multiple ListObjects, it looks to me like Selection.ListObject returns the one that was created earliest. With the selection below, that means that it returns Table1.

In Conclusion

Now I have even more reasons to never use Selection.ListObject or Selection.PivotTable! They’re both quirky, and I don’t think I could expect users to know that if they select more than one table, they’ll get the oldest one (or for that matter, that they’d get any one). And for a pivot table, it would be kind of fussy to insist the upper-left corner of the selection be in the pivot table.

I’m sticking with ActiveCell. I think that’s the way Range.PivotTable and Range.Selection should have been designed as well. In other words, like Range.PivotTable does, only using ActiveCell instead of the upper-left corner.

Changing Pivot Field Label’s Case

Posted on March 1, 2015 by Doug Glancy

After last week’s prodigious post I return with a tiny tip: how to change the case of the text in a pivot field’s (or item’s) label. Here’s an example of the problem:

Above, I’ve noticed that drunken monkeys have hacked my computer and tried to make me look bad by messing up the “Country” label. “Not so fast!” I say and retype it correctly. However, when I click back out of the cell the label reverts to the weird capitalization. Hmmm, perhaps these fiendish monkeys are smarter than I thought!

But no, it’s just one of those Excel quirks. Simply retyping the same letters with the correct case doesn’t fix it. You have to actually change or add a character, leave the cell, and then go back in and fix the case, like this:

Step 1 – add an “x” and hit Enter

Step 2 – type it the way you want it

I could have sworn I’ve seen this same quirk elsewhere in Excel, or perhaps in Windows Explorer, but I looked around and don’t find similar behavior anywhere. If you have, please let me know where, and if this trick works.