Howdy, folks. Jeff here again. I’ve been sharpening up some code to manually filter a PivotField based on an external list in a range outside the PivotTable. It works blazingly fast. Unless there’s dates as well as non-dates in my PivotField. In which case it errors out.
Try this:
Put “Pivot” in A1
Put =TODAY() in A2
Put =VALUE(TODAY()) in A3
Now make a PivotTable out of that data.
Now put this code into the VBE and step through it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub WhatThe() Dim pf As PivotField Set pf = ActiveSheet.PivotTables(1).PivotFields(1) With pf .NumberFormat = "d/mm/yyyy" .PivotItems(1).Visible = True .PivotItems(2).Visible = True .NumberFormat = "General" .PivotItems(1).Visible = True .PivotItems(2).Visible = True End With End Sub |
If the same thing happens to you as happens to me, you will either be speaking aloud the title of this routine, or you will be speaking aloud this:
Unable to get the PivotItems property of the PivotField class.
Go type these in the immediate pane:
|
1 2 3 4 5 6 7 8 |
? .PivotItems(1).name 41588 ? .PivotItems(1).visible True ? .PivotItems(2).name 10/11/2013 ? .PivotItems(2).visible Error 2042 |
What the…?
Now try these:
|
1 2 3 4 5 6 7 |
? ActiveSheet.PivotTables(1).PivotFields(1).numberformat General ActiveSheet.PivotTables(1).PivotFields(1).numberformat = "d/mm/yyyy" ? .PivotItems(2).name 10/11/2013 ? .PivotItems(2).visible True |
So it seems can’t do certain stuff to a PivotItem if that PivotItem is a date but your PivotField number format is set to General.
That’s weird.
Equally weird, try this:
Select the PivotTable, and record a macro as you change it’s number format back to General.
|
1 2 3 |
Sub WhatThe_Part2() ActiveSheet.PivotTables("PivotTable14").PivotFields("Pivot").Name = "General" End Sub |
What the …? Change the PivotField Number Format, and you get a macro that tells you that you changed the PivotField name!
So what happens if you run that macro? Well, it changes the name of the PivotField:
It does nothing to the number format.
Strangely enough, I found some info on this problem at one of my most revisited blogposts that I had somehow missed: Jon Peltier’s Referencing Pivot Table Ranges in VBA
Stranger still, the answer was by Jon Peltier back in 2009 in relation to a question asked by….wait for it…me. Don’t know how I missed that. Must have been sleeping.
So I’ve come across this problem before, found an answer, completely forgotten about it, and then wasted 2 days trying to work out what the problem was, purely so I could Google my own answered question.
I’m going to read through all 238 ( and counting) comments in that thread and see what else Jon has told me over the years I’ve been learning VBA.
There’s also something on this at stackoverflow
Jeff
–edit–
Jon’s method was to loop through the pivot items, and compare the pivot item caption to what he was looking for:
|
1 2 3 4 5 6 |
For Each pi In pt.PivotFields("Order Month/Year").PivotItems If pi.Caption = Format(TheDate, "m/dd/yyyy") Then ' or If DateValue(pi.Caption) = TheDate Then '' FOUND IT End If Next |
But now I know it’s probably easier just to change the format of the PivotField.
—edit 30 May 2014 —
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?)
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.
— edit over —
Jeff, I can sympathise entirely. I keep pretty good notes on various subjects, using Word and some handy bookmarks and hyperlinks to jump around from the contents page. But only those readers who have been through the experience will know how this feels. To spend a couple of great days battling through some weird scenario, emerge triumphant on the other side, battered but wiser. You open up your notes file ready to commit your latest gem to the section entitled ‘never need to know this again’, only to find you`ve been there, seen it, cracked it, and thoroughly documented it – maybe not even six months ago. What is the word for when elation instantly changes to an embarrassed despondency. A mix of triumph and disaster. Triaster? Disumph?
i think we can do by this way also :)
select date column > press Ctrl+Shift+#
Triaster sounds better!
Actually, I’ve just realized that neither Jon’s approach nor mine will solve the problem. In the code snippet from Jon’s site, he uses this to identify the correct PivotItem. But having identified it, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.
And as for my suggestion – change the format of the PivotField – you can only do that if your entire PivotField contains items that are all formatted the same way. It is in my very basic example. But if you add some text in cell A3, then you can no longer set the PivotField format…attempting to do so generates an error.
So my blazingly fast filtering routine will have to simply warn the user that dates will not be filtered. Stink!
@Krishna: That formats the underlying cells, but not the Pivot. Furthermore, as per comment above, if you have dates mixed in with say text then the PivotField doesn’t actually have a format (not even General) so any attempts to change it result in a run-time error.
Jeff, Thanks for shedding more light on the vexing topic of manually filtering by dates in PivotTables.
You noted,
In the code snippet from Jon’s site, he uses this to identify the correct PivotItem. But having identified it, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.
Using Jon’s approach, is there any reason one can’t simply use the PivotItem object assigned to Pi to hide the found PivotItem?
Both of these snippets appear to work with mixed data types in the field….
Or…
Hi Jeremy. Sorry, your comment has escaped my notice until just now.
The issue remains that unless you have Excel 2013, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.
Run this code on a new sheet, and you’ll see what I mean:
IF you’re running Excel 2010 (and presumably earlier versions too) you’ll get this:
Run-time error ‘1004’:
Unable to set the Visible property of the PivotItem class
And if you hit Debug, you’ll see that while the code could quite happily make those items visible while there was a date format applied to the field, it errored out when a General format was applied to the field.
…and if you have mixed data types in that field (e.g. maybe some text) then you can’t simply set the field’s format to Date, because Excel won’t let you change formats for fields with mixed data types.
Interestingly, this seems to have been fixed in Excel 2013 i.e. the above code runs without error.
Hi again, Jeremy. I see you tested this in the field and had no problems. Can you advise what version of Excel are you using, and what happens if you run the code snippet I posted in the above comment?
Cheers
Hi Jeff, I currently use xl2013. When I posted in Nov 2013, I only had xl2010 installed and I was able to hide date values in a PivotField that was not formatted as a date using the code snippet I posted.
I ran the WhatThe_v2 code just now on both xl2013 and xl2010 and didn’t receive an error on either.
I re-ran with a statement a statement to add the RowField, which is what you probably intended. Still no errors.
Set pt = pc.CreatePivotTable(TableDestination:=rng)
pt.PivotFields(1).Orientation = xlRowField
BTW Most people call me Jerry, but you're welcome to call me Jeremy since you were kind enough to fix my first-time Blog-poster error of using vBulletin style forum [Code] tags. :)
Thanks Jerry. Sorry ’bout getting the name wrong. That’s really weird…the code bombs out for me big-time. The xlRowFIeld thing doesn’t matter, by the way.
I’ll have to get some other folk to run the code on different machines, and see what the result is.
It turns out this is a regional issue. According to IronyAaron in the comments at http://social.msdn.microsoft.com/Forums/office/en-US/01cb61c7-5e68-4a45-aeff-a70c6dbfe00f/excel-2007-accessing-pivotitemvisible-gives-error-if-the-field-item-value-is-a-date :
When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formated string. This therefore causes VBA to fail when recognizing Date variables.
Only real solution is to get the user to change regional settings in Windows to US. Good luck with that.
Hi Jeff!
I also had a problem with dates in PivotTable. I’ve found your blog and try every solution. Nothing worked.
Because I’m working on Office2010 and there is no possibility to change the MS Office soon in our company, I had to figure something that will work. And I’ve thought about slicers. They are in Offce 2010 and they are nothing but filters. THAT WORKS. A little different than regular filters but hey, at least it’s something.
“Data” in polish means singular date.
Dim str As String
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("Pivot_Table1"), _
"Data").Slicers.Add ActiveSheet, , "Data", "Data", 186, 760, 144, 188
For Each Pi In ActiveSheet.PivotTables("Pivot_Table1").PivotFields("Data").PivotItems
str = Replace(Pi.Caption, "/", "-") 'dates are with "/" but slices are with "-"
ActiveWorkbook.SlicerCaches("Fragmentator_Data").SlicerItems(str).Selected = False
Next Pi
'ActiveWorkbook.SlicerCaches("Fragmentator_Data").Slicers("Data").Delete 'option
Thanks for the tip, Michael.
Hi Michael, your hint is great!
I know this is an old thread but I just want to add my two cents worth which may help others with the same problems. Here is my approach to solving the above-mentioned issues with filtering dates on computers with different regional settings/date formats.
1- I create a helper column which converts the date fields to its numeric value using the value() function.
2- I use this helper column in the pivot table’s filter instead of using the original date field.
3- Whenever the end-user inputs his start/end dates, I convert those to numeric values as well and store in a separate hidden cell.
4- I then use all those numeric values in my vba scripts to filter the pivot tables, completely eliminating issues with date format, as you’re just working with numbers.
Thanks, hopefully this helps others.
Good workaround. Thanks Jason.