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]

Immediate window shows <30/06/2013 Records: 0

 

[Jerry Sullivan]

Could you provide an example workbook (with any sensitive data removed/changed)?

You could post it to a hosting site like box.com and post a link, or send me a PM and we'll exchange email addresses.

 

[Jerry Sullivan]

Thanks for sending your example workbook.

The behavior of the first and last grouped PivotItems that start with < or > is that they allow their Visible property to be Set, but do not allow their Visible property to be Read.

It could be related to having your Regional Setting set to use dd/mm/yyyy date formats. It's somewhat common to have problems using VBA to handle dates in PivotTables when the Regional Settings are other than mm/dd/yyyy.

This modified code seems to work with your example pivot. It just sets the Visible property to the desired state without checking to see if it's already set to that state. For pivotTables with large numbers of PivotItems, that difference can really make a big difference in the speed. For your data that is grouped by years- the difference should be insignificant.

Sub HideItems()
'---Sets manual PT filters to show all pivotitems in field except
'     those starting with "<" or ">"
 Dim i As Long
 Dim sFirst As String
    
 With ActiveSheet.PivotTables("PivotTable7").PivotFields("Years")
   '--set last item visible to avoid error from hiding all items.
   .PivotItems(.PivotItems.Count).Visible = True
   For i = 1 To .PivotItems.Count
      sFirst = Left(.PivotItems(i), 1)
      .PivotItems(i).Visible = (InStr(1, "<>", sFirst) = 0)
   Next i
 End With
End Sub

Please let me know if this works on your system too.

 

[anabuan]

same error occurs "unable to set the visible property of the pivot item class" and it stops in .PivotItems(.PivotItems.Count).Visible = True

 

[Jerry Sullivan]

I changed my regional setting to English (UK) and the code still worked for me on PivotTable7 of your example workbook.

Earlier in this thread you said the first suggested macro worked on one Pivot, but not on another. Which Pivot(s) in your example workbook are giving you that error and which if any appear to work?

 

[anabuan]

weird it does work in the first code initially in PivotTable 7 but now when I copy the initial code it does have an error?

 

[Jerry Sullivan]

Try changing the Number Format of the Year field to a Date format. In your example workbook, PivotTable7 currently has the number format "General".

Do that under Field Settings - not by selecting and formatting the range of cells.

Excel 2007 accessing PivotItem.Visible gives error if the field item value is a DATE

 

[anabuan]

I have tried changing the number format in the field settings into dd-mm-yyy but still not working.

 

[anabuan]

Finally the code below seems to work in my problem. I have found the solution in this website Grouping Shows Items Before and After Date Range. Sorry for the trouble but still many thanks for the assistance.

Sub HideItem()


Dim pi As PivotItem
On Error Resume Next


Application.EnableEvents = False
pt.ManualUpdate = True


For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Years").PivotItems


Select Case Left(pi.SourceName, 1)
Case "<"
pi.Visible = False
pi.Caption = " " '1 space
Case ">"
pi.Visible = False
pi.Caption = " " '2 spaces
End Select
Next pi


For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Week Starts").PivotItems


Select Case Left(pi.SourceName, 1)
Case "<"
pi.Visible = False
pi.Caption = " " '1 space
Case ">"
pi.Visible = False
pi.Caption = " " '2 spaces
End Select
Next pi






pt.ManualUpdate = False
Application.EnableEvents = True


End Sub

 

[Jerry Sullivan]

I'm glad to hear you found something that works. The approach of using For each ... to step through each object, then pi.Visible=False is the same as that we tried in Post #10. One difference is that Post #10 code tried to read the value of the Visible state and the code you found that works doesn't.

The other differences between the codes should not affect whether they run or error. I'd avoid renaming the caption property to blank spaces unless you have a good reason to do that.

Could you please try this version and let me know if it works? It's a mix of post #10 and #13.

Sub HideItems2()
'---Sets manual PT filters to show all pivotitems in field except
'     those starting with "<" or ">"
 Dim pvi As PivotItem
 Dim sFirst As String
 
 With ActiveSheet.PivotTables("PivotTable2").PivotFields("Years")
   For Each pvi In .PivotItems
      sFirst = Left(pvi, 1)
      pvi.Visible = (InStr(1, "<>", sFirst) = 0)
   Next pvi
 End With
End Sub

Thank you for sharing your findings!