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.

Answers


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.


Need Your Help

How do I make the action bar fixed? UPDATED

java android xml

UPDATE: I figured out the answer, but I'm too low in ranking to share my answer.

Writing Intelli-J inspections?

intellij-idea code-hinting inspections

How would I go about writing my own Intelli-J inspection? I'm looking for some general guides or resources.