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

 

[Jerry Sullivan]

It sounds like you are Grouping that field by Years, and you want to hide the first and last groups that start with the character ">" or "< ".
Is that correct?

You can filter for those items without the need for named ranges by stepping through the PivotItems and looking at the first character.

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")
   For i = 1 To .PivotItems.Count
      sFirst = Left(.PivotItems(i), 1)
      If IsError(.PivotItems(i).Visible) Then
         .PivotItems(i).Visible = False
      Else
         If .PivotItems(i).Visible = (InStr(1, "<>", sFirst) > 0) Then
            .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
         End If
      End If
   Next i
 End With
End Sub

 

[anabuan]

Thanks, Jerry. It works! But if I use it in different PivotTable and just change the name of Pivot Table unable to set the visible property of the pivotitem class error occurs...

 

[Jerry Sullivan]

What is the specific error message and at what line of the code does the debugger stop?

 

[anabuan]

Error says unable to set the visible property of the pivot item class it it stops in

.PivotItems(i).Visible = Not (.PivotItems(i).Visible)

 

[Jerry Sullivan]

When the debugger stops, paste this expression in the Immediate Window of the VBE:

?ActiveSheet.PivotTables("PivotTable7").PivotFields("Years").PivotItems(i).Visible

Modify PivotTable7 to match the name your PivotTable
Hit enter and post what value come back (or what error).

Do the same with:
?i

 

[Jerry Sullivan]

Sorry, but I've got to wrap up for the night. I'll be glad to help more tomorrow (or perhaps someone else is online who will join the discussion).

 

[anabuan]

When I copy your code to immediate window and hit enter error is "Runtime error 1004 Unable to get the PivotTables property of the worksheet class". No worries its understandable that you have to sleep as its already late.. it maybe a long day for you

Thanks for helping people like me...

 

[anabuan]

by the way if I type ?i in the immediate window result will be 1.

 

[Jerry Sullivan]

Try this modified version. It uses For Each instead of the Index numbers of the PivotItems.

Sub HideItems()
'---Sets manual PT filters to show all pivotitems in field except
'     those starting with "<" or ">"
 Dim i As Long
 Dim pvi As PivotItem
 Dim sFirst As String
 
 With ActiveSheet.PivotTables("PivotTable7").PivotFields("Years")
   For Each pvi In .PivotItems
      Debug.Print pvi.Caption & " Records: " & pvi.RecordCount
      sFirst = Left(pvi, 1)
      If IsError(pvi.Visible) Then
         pvi.Visible = False
      Else
         If pvi.Visible = (InStr(1, "<>", sFirst) > 0) Then
            pvi.Visible = Not (pvi.Visible)
         End If
      End If
   Next pvi
 End With
End Sub

The Debug.Print statement will print some info to the Immediate Window that could be helpful. If the code above errors, please post the info sent to the Immediate Window.