What caused that PivotTableUpdate? Part Two.

In Part One, we considered the problem of determining what actually triggered a PivotTableUpdate (or it’s equally gormless twin, the PivotTableChangeSync event), with a view to identifying when a specific PivotField is filtered. Today we’re going to take a look at how we can find out more about what triggered those events. And what better place to glean information about the last action the user performed than here:


That’s right, the Undies stack. (That’s what we call it down-under). Or rather, the Undo stack to you uptight northerners. (Ok, enough of the innuendo and Double entendre.)

Go on, then…show us what’s in your undies…er…undo stack, Jeff:

Undo Pivot Actions 5

Wow: all of the above relate to some kind of action on a PivotTable. If only we could access that list, we’d have a pretty rich source from which to answer the title of this post. And we can indeed do just that.

If you want everything in that list, then you can use this code from MVP Siddharth Rout at msdn:

For i = 1 To xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount
UnDoList(i) = xlApp.CommandBars("Standard").Controls("&Undo").Control.List(i)

…and if you just want the last undo item, you can use this:

Let’s take a look at how all the different things that raise a PivotTableUpdate event get reflected in that Undo list. If an action isn’t listed in the below table, then as far as I know from my rather in-exhaustive testing, it doesn’t raise a PivotTableUpdate event.

Actions and associated undo stack text v2

There’s a few things worth noting about the above.

  • Firstly, we can now clearly determine whether the update was caused by filtering, a refresh, a structure change (in which case the Undo Stack just says ‘Pivot’, or other less common tweaks.
  • Secondly, while we can use this to confirm whether or not a PivotTableUpdate event was in fact caused by someone adjusting a PivotFilter, we still can’t tell which filter.
  • Thirdly, it’s surprising just how many things trigger an update – which is why it will be good to call out filter changes explicitly in the event that we want to sync lots of large pivots.
  • And finally, one of the actions – adding/amending/deleting a Calculated Field – actually clears the Undo Stack. How weird is that?

Okay, that’s enough for today…I’ve got to go cook my dinner. (My wife is in Spain with the kids, and so apparently it won’t cook itself). Tune in next time, when we’ll look at how we can write a routine that leverages off the undo stack, and that also helps us determine not only that a PivotField was filtered, but which PivotField it was.

What caused that PivotTableUpdate? Part One.

Over at MSDN, ToWIZ writes:

I’m looking for a detailed guide that would help users understand the details of PivotTable events and their functioning. The documentation has only one sentence for each event. Surely Microsoft doesn’t consider this to be a detailed guide?

Apparently so.


  • The PivotTableUpdate event occurs after a PivotTable report is updated on a worksheet.
  • The PivotTableChangeSync occurs after changes to a PivotTable. This event can be used only in Excel 2010 projects.

And let me get this straight: reading between the lines on this extensive documentation, you’re telling me that the new event does the same thing as the old event, except for the fact that it only works in Excel 2010 or later? Wow. Quite some improvement.

I can’t find a single thing that differentiates these two events – either on the web or in practice. Here, you try. Put this in a Sheet Module where you’ve got some pivots, and play around with them:

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Debug.Print "PivotTableChangeSync: " & now()
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Debug.Print “PivotTableUpdate: ” & now()
End Sub

Did you find a single case where one got triggered and the other didn’t? Me neither.


Eric van Rooijen did: Ticking the ‘Defer Layout Update’ option in the PivotFilter Pane means that only the Worksheet_PivotTableChangeSync gets triggered when changing PivotTable Fields.

You’ve almost got to wonder if someone on the team at Redmond misheard the brief for that 2010 addition. I’m sure they meant to introduce a new handler called PivotFieldChangeSync that would allow us to do something that we currently can’t – work out exactly what a user was doing to a PivotField (e.g. filtering a particular field). Instead we got this rehash with less scope than the old thing, as near as I can tell.

Which is a pity. Because PivotTables are the best thing about Excel, and PivotFilters (and now Slicers) are how users interact with that best thing. And here we are in Excel 2013 with still no good way to find out what PivotFilter a user just changed.

Why would you want to know that? Plenty of reasons…one of which is so that then you could efficiently sync a whole bunch of pivots in a dashboard in the case that slicers aren’t an option (e.g. the pivots are on different caches, or users have an earlier version of Excel that doesn’t support slicers). In that scenario, relying on just one of the above event handlers to run the code is damned inefficient, because:

  • It would get triggered by any old change that you make to a Pivot.
  • Even when it does get triggered by someone actually changing a PivotFilter, the question remains…which one did they change? Those events won’t tell you…

Don’t go thinking that a Worksheet_Selection change is going to help you work it out, because clicking on a PivotFilter or Slicer does not change your selection. And don’t go thinking you can use a Worksheet_Change event to capture the filter refreshing neither…when a PivotTable gets updated, the whole PivotTable gets updated, meaning that Worksheet_Change event returns the entire range that a PivotTable occupies – not just the cell behind the filter that a user just changed.

All this means that if you wanna say sync a whole bunch of pivots – and slicers aren’t an option – then relying solely on one of those insensitive Update events alone means you’ll have no choice but to sync pretty much every visible field that appears in the master pivot…something that may result in unacceptable delays to users if there are lots of pivots with lots of fields and with lots and lots of pivotitems in them. It could take many many minutes to iterate through all of them.

Sure, you can build some sort of userform control or array of shapes (one for each pivotfield) that the user clicks before they say filter a particular field. But that sounds complicated and quite likely bespoke to the dashboard concerned.

Or perhaps you could programatically put some invisible shapes in front of each PivotFilter, so that when when the user goes to click the PivotFilter they actually click those shapes instead, meaning you can then use application.caller to work out which shape – and therefore which PivotFilter – they clicked on. But you’d have to use SendKeys to open the filter that they just thought they clicked on, and you’d have to ensure those invisible boxes always moved with the PivotTable. Pretty invasive and unreliable stuff, really. And pretty complicated to set up.

Screw that…how ’bout a generalised solution that works right out of the box on any pivot, with no setup whatsoever?

How would you capture changes to specific PivotFields and the like?

Got a good method? Let us know in the comments. Got no idea? Tune in next time to see my approach.

Playing Peekaboo with the Used Range

I was trying to reset the used range in a spreadsheet template I inherited the other day, that had a much bigger used range than it should have had. Damned if I could get it to reset. Tried deleting rows and columns below and to the right of the actual used range. Tried this:

Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long

For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count

End Sub

Still no joy.

Then after much playing around, I discovered the problem. Several problems, really:

  • If you hide a row – or adjust it’s width, then the UsedRange is extended down to that row. But only for rows. And deleting all the rows below your ‘actual’ used range soon fixes this problem.
  • Hiding columns or adjusting their width does nothing to the used range. Unless you hide a column, and then unhide it by manually dragging on the boundary of the hidden column in order to resize it. Then you just screwed up the UsedRange bigtime: the used range now extends to that column, and it is devilishly hard to reset it again. Deleting all columns to the right of your ‘actual’ used range does NOT fix the problem.

Try it. You’ll find that the extended used range this gives is much more persistent than the Spanish Football Team.

After much experimentation, I found that you can only reset the used range back to what it should be if you:

  1. Hide that pesky column again.
  2. Unhide that pesky column again, using the Right Click > Unhide method.
  3. Delete that pesky column.
  4. Delete the perfectly innocent column that took it’s place.

Then and only then could I get the used range to go back to it’s cage. Weird. But might explain why you’ve had used ranges in the past that you simply could not get to behave.

(UPDATE:As demonstrated by snb’s code, the weird result you get by resizing a hidden column disappears if you close and then reopen the workbook.)

In the course of all this, I noticed that if you hide a row, then if you put the cursor over the hidden row and double-click when this icon comes up:
Autofit Row
…then the row is unhidden.

But if you hide a column, and then double click when this comes up:
Autofit Column
…absolutely nothing happens. What’s with that?

In both cases, the Macro Recorder spits out Rows(“25:25”).EntireRow.AutoFit or Columns(“M:M”).EntireColumn.AutoFit as the case may be. But in the case of hidden columns, it just doesn’t seem to do anything.

Weird again.

NOT learning from my Errors

I was writing some formulas today that need to return TRUE if a search term appears in a cell, and FALSE otherwise. For instance, I wanted to know if FOO appeared in FOOBAR or not.

Originally I was using FIND, along these lines:

But then had some issues with case that screwed things up:

Given that for my purposes FOO was as good as Foo, I decided to replace the FIND function in this with the SEARCH function, on account of SEARCH being an insensitive bastard. (Can I say that here? No? Oops…sorry!).

So I manually edited the formula, and replaced FIND with SEACH. Then cut and pasted that formula in lots of other places. Then did lots of analysis with the output. Without learning from the error of my ways:

Whoops…that’s not how you spell SEARCH! Why didn’t you tell me Excel? Oh…because of this:

Which don’t mean there ain’t no FOO (or Foo) to be found. Rather it means “Yes, I am in fact returning an error – thank you for asking – because I have no idea what this damn SEACH does.”

Murphy’s law: I never noticed that I’d screwed it up until right at the very end of my days work.

Still, not noticing till the very end is better than not noticing.

Or as Excel would put it:
=NOT(ISERROR(SEARCH(“not noticing”,”not noticing till the end is better than not noticing.”)))

Learning from my Errors

There’s an annoying bug in VBA whereby if you’re trying to change the .visible status of a PivotItem, and if the PivotField had a number format set to General, and if you live in New Zealand, then you’re out of luck:

Unable to set visible property

If you live in the US however, you’ll be fine. No error for you.

Don’t believe me? Either change your Windows region to New Zealand and run this code, or swing down to my place and see it for your own eyes. Here’s where you’ll find me:

247 Rintoul Street

(Aside: Check out those awesome ocean views. Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).

Ok, so this issue isn’t just an issue for New Zealanders…it actually affects any place where you haven’t got your Windows ‘region’ set to US, with New Zealand being the only place where I’ve actually encountered such egocentric behavior to date. (I don’t get out much. Or rather, they don’t let me out much. Or rather they make it clear that I can go out, but I can’t come back in.)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

This nasty bug caused quite a bit of workaround in my FilterPivot routine. I used to do this horrible check on every single item in a potentially exhaustively long list of PivotItems in order to avoid the possibility of an error caused by this unlikely combination occurring:

If Not IsNumeric(Pi.Value) Then
'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
If IsDate(Pi.Value) Then
If Not bDateWarning Then
On Error GoTo ErrHandler
Err.Raise Number:=997, Description:="Can't filter dates"
On Error Resume Next
End If
Else: Pi.Visible = True
End If
Else: Pi.Visible = True
End If

But prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

So now I can just do this:
pi.Visible = True

Or rather, I could just do that if everyone had Excel 2013. But they don’t. So I can’t. I still have to somehow catch this error. And as written above, my code rather inefficiently looks for possible trouble caused by a combination of things that is probably unlikely to occur. (I mean, how many people would dare to have their Windows region set to a non-US region while trying to filter a PivotItem that happens to be a date in a PivotField that happens to have a General format?) All that preemptive error checking can’t be good for business.

The error of my ways?

I’m sure you’ve already seen what looks to be like the error of my ways… why bother checking for errors just so I can avoid them? Why not embrase them: just plow ahead, and if the s#!t hits the fan, just deal with it. Something like this:

On Error Goto Errhandler
pi.Visible = True

'some other code

If Err.Number <> 0 Then
Select Case Err.Number
Case 1004 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
Case Else 'Some other error code here

Great: now that bug fix code in the Errhandler only gets fired up in the rare event that it is actually triggered. Sure beats neurotically checking each and every PivotItem to see if it might cause an issue.

Unfortunately in this particular case the code snippet in question sits within a larger loop, and the code immediately before needs to have an On Error Resume Next statement applied. That’s because in order to work out whether a PivotItem should be hidden, I’m adding it to a Dictionary object that also contains my desired filter terms, in order to see if it matches any of those filter terms. Which looks something like this:

On Error Resume Next
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number <> 0 Then
pi.visible = true

So I’d need to put an On Error Goto Errhandler before the pi.Visible = True bit so that my bug fix code in Errhandler would get triggerred, and an On Error Resume Next bit after it, so that the Dictionary test occurs for the very next item. And those will get executed for every single PivotItem – which kind of defeats the efficiency ‘dividend’ of putting my handling code within Errhandler. So I figure I might as well just do this:

pi.Visible = True
If Err.Number = 1004 Then 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
End If

Two steps forward, one step backwards.

Maybe I shouldn’t have that On Error Resume Next in there in the first place…maybe I should catch errors from the Dictionary.add in Errhandler too, or even do the dictionary check in another procedure – something that Dick mentions here. Anyone got any advice here?

All I know is that this is a lot of work-around for a combination that is pretty unlikely, but potentially fatal to someone’s analysis.

Pop quiz

Question One

You want to calculate a running (i.e. cumulative) total of the Data column.
Which of these formulas should you put in B2 and drag down, and why?

Question Two

You want to calculate a running (i.e. cumulative) total of the Data column, and subtract 1 from it.
Which of these formulas should you put in B2 and drag down, and why?


If you’re ambivalent as to the approach you would take, download and try out a slightly revised thought experiment in the attached file. Then you won’t be ambivalent.


This has a more realistic data set, where instead of subtracting 1 from the cumulative total, you want to subtract a varying list of cumulative expenses, so you can work out the cumulative net profit:
Pop Quiz v5

Take it that inserting/deleting rows is not an issue (assume the structure is locked down).

Note that this is intended to be a thought experiment/illustration about a common approach which happens to be very resource intensive, and a better solution that works just fine provided you don’t do anything else within that formula but calculate a cumulative total.

But feel free to post alternatives.

How do you know if a ListObject has the autofilter applied?

If you try to filter a ListObject, and someone has turned the entire filter off by deselecting the Filter icon from the Data tab, then you’ll get an error. But how the heck can you easily test if the filter is on or not?

If you fire up the macro recorder, and click the Filter icon a few times to toggle it on and off, then you just get this:

You can write If Selection.AutoFilter = TRUE then... but this simply serves to toggle the autofilter from it’s current state, and always returns true.

It seems to me that the only thing you can do is something like this:
Function FilterIsOn(lo As ListObject) As Boolean

Dim bOn As Boolean

bOn = False
On Error Resume Next
If lo.AutoFilter.Filters.Count > 0 Then
If Err.Number = 0 Then bOn = True
End If
On Error GoTo 0
FilterIsOn = bOn
End Function