How to deselect a pivot item name which has < > for dates

[anabuan]

I am trying to create a pivot table that would show all the months in a year even if it doesn't have any data then later on this will be a basis for my charts. So I went I click on the "show items with no data" in field settings layout & print. However, excel automatically shows less than a specific date and greater than a specific date. e.g. <30/06/2013 and >23/06/2014. I was trying to get rid of those by naming a specific cell that contains <30/06/2013 as min and >23/06/2014 as max. Then I am trying to create a vba that would automatically de-select those by looking into those named cell. However, there is always an error.

I am just a beginner in vba so I would be grateful if you could help me. Thanks.

Below is my code;

Dim min As String
Dim max As String
Dim pvtItem As PivotItem


min = Range("min").Value
max = Range("max").Value


With ActiveSheet.PivotTables("PivotTable7").PivotFields("Years")
.ShowAllItems = True
For Each pvtItem In .PivotItems

pvtItem.Visible = pvtItem.Name = "min"
pvtItem.Visible = (pvtItem.Name = max)

Next
End With

 

[anabuan]

same error occurs "unable to set the visible property of the pivot item class" and it stops in pvi.Visible = (InStr(1, "<>", sFirst) = 0).

the code that I found still needs to be run twice in order to remove the blanks totally. can't see why it needs to be done twice.

 

[Jerry Sullivan]

I just noticed the code that works when run twice has a
On error resume next statement. That causes the code to continue even if an error occurs.

Try commenting out that statement. My guess is it will throw the same error.
What that code is doing is renaming the items to captions that can be filtered without error.

You could employ that concept by renaming to something else like "before years" "after years".
I don't like blank space because you can't easily read the value.
The visible = false should come after the rename of the caption.

(Typed from my iPhone)

 

[Jerry Sullivan]

I was away from my computer earlier. Here's the code I had in mind.

Sub HideItems3()
'---Sets manual PT filters to show all pivotitems in field except
'     those starting with "<" or ">"
 Dim pvi As PivotItem
 
 With ActiveSheet.PivotTables("PivotTable2").PivotFields("Years")
   For Each pvi In .PivotItems
      Select Case Left(pvi.SourceName, 1)
         Case "<"
            pvi.Caption = "Before"
            pvi.Visible = False
         Case ">"
            pvi.Caption = "After"
            pvi.Visible = False
         Case Else
            'do nothing
      End Select
   Next pvi
 End With
End Sub

 

[anabuan]

awesome! that solves my problem.. many thanks Jerry

 

[Iakovosian]

It seems that there is now another way to prevent 'out of date range items' from appearing in drop down filters, pivot tables, and pivot charts when grouping date fields. This works even when the 'Show items with no data' option is selected in the date field settings.

In the 'PivotTable Options' window, on the 'Totals & Filters' tab, there is an option to 'Allow multiple filters per field'. With this checked, you can deselect the first/last groups with '<' and '>' in their names from the filter drop down in the 'Field List'. Having done that, any further filter updates (from slicers, timelines, etc.) won't see those two groups and - most importantly - won't keep making them show again whenever another filter is changed!

I'm on the Office 365 Monthly Channel, but this may also be available in earlier versions. I hope this helps someone else too.