Expand only those pivot items with more than one entry (Excel VBA)
Ok, so I am trying to have Excel VBA code go through a pivot table and expand out only those entries in the last row field that don't have ONLY one entry in them. By default, the last row field is collapsed.
What I have so far:
Sub Test() With ActiveSheet.PivotTables("OBJSUMMARYPT") Dim pvtitem4, pvtitem5 As PivotItem For Each pvtitem4 In .RowFields("OBJECT CODE GROUPING").PivotItems For Each pvtitem5 In .RowFields("CONTRACT & CONTRACT TITLE").PivotItems If pvtitem5.Name <> "NON-CONTRACT" And pvtitem4.RecordCount > 0 And pvtitem5.RecordCount > 0 Then .PivotFields("OBJECT CODE GROUPING").PivotItems(pvtitem4.Name).ShowDetail = True End If Next pvtitem5 Next pvtitem4 End With End Sub
This expands too many of them, because it isn't cross-referencing if there are any pvtitem5s in pvtitem4 that have records, and I am not figuring out the syntax to do so.
I recognize that single entries that aren't listed as "NON-CONTRACT" will be shown and I don't take issue with that; that is fine to happen. I want to get fewer false positives than what I am getting now.
Found the solution:
Sub Test() With ActiveSheet.PivotTables("Table") .PivotFields("Cat6").PivotItems("TestObject").Visible = False Dim pvtitem4, pvtitem5 As PivotItem For Each pvtitem4 In .RowFields("Cat5").PivotItems On Error Resume Next If pvtitem4.DataRange.Column = Null Then GoTo pvt4 .PivotFields("Cat5").PivotItems(pvtitem4.Name).ShowDetail = True pvt4: Next pvtitem4 .PivotFields("Cat6").PivotItems("TestObject").Visible = True End With End Sub
It works by filtering out what I don't want expanded, then determining which other categories are still visible in the pivot table and expanding them, then unfiltering.