Pivot Table, Show/Hide Rows

[mgmirvine]

Hello,

First, thank you for reading my question and offering any help!

I would like to have a button that shows/hides a particular field in the row section of my pivot table. For example, say I have a small PT with State, County, City, Population. I might just want the row State to just see population by state. Alternately, I might want just want State/County rows; State/City rows; or State/County/City.

So, I would like 3 buttons, one to show/hide State, one to show/hide County, and one to show/hide City.

I am by no means an Excel/VBA expert. While I can figure out how to change the visbility a particular state/city (pivot item) through VBA, that really isn't my goal.

Again, any guidance you can provide is greatly appreciated.

 

[Anthony47]

Well, I suggest that you record a macro while selectively enable or disable each of the available "PivotItems" of your PivotTable; then publsh the code that is genarated and we will modify it according your request.

Bye

 

[Jerry Sullivan]

Hi all,

I was going to post almost the exact same suggestion as Anthony47, and then realized
what makes this just a little trickier is preserving the relative order.
The Position Numbers will vary depending on which combination is visible.

Here is one approach to address that wrinkle.

Use this function with 2 parameters (Field and Hide/Show)

Public Function PT_RowField_Hide_Show(sField As String, bHidden As Boolean)
    Dim lPos As Long, i As Long
    Dim vFieldOrder As Variant
    Application.ScreenUpdating = False
    
    vFieldOrder = Array("State", "County", "City")
    
    With ActiveSheet.PivotTables("PivotTable1")
        If bHidden Then
            .PivotFields(sField).Orientation = xlHidden
        Else
            .PivotFields(sField).Orientation = xlRowField
            lPos = 1
            For i = UBound(vFieldOrder) To UBound(vFieldOrder)
                With .PivotFields(vFieldOrder(i))
                    If .Orientation = xlRowField Then
                        .Position = lPos
                        lPos = lPos + 1
                    End If
                End With
            Next i
        End If
    End With
End Function

Now you can make 6 calling Subs...one for each button.
Here are 2 examples to get you started.

Sub CityField_Hide()
    Call PT_RowField_Hide_Show(sField:="City", _
        bHidden:=True)
End Sub

Sub StateField_Show()
    Call PT_RowField_Hide_Show(sField:="State", _
        bHidden:=False)
End Sub

 

[Anthony47]

While I think that Jerry's proposal will work I was just going to offer 3 toogle macros very similar whose key statement is (for example)

.PivotItems("State").Visible = Not (.PivotItems("State").Visible)

I asked for the recorded macro to get the correct pivottable and pivotfields names.

Bye

 

[Jerry Sullivan]

Hi Anthony,

If I'm understanding the original post, State, County, City are PivotFields, not PivotItems.

If they are PivotItems then your suggestion is closer to what is needed.

 

[Anthony47]

Well, in reading again the initial post I now come to the same your understandig; however I find difficult believing that both State and City (let me ignore the intermediate County) are PivotFields. Again the recorded macro would clarify this aspect.
Nice sharing this thought with you.

Bye

 

[mgmirvine]

All, thank you so very much for the tips. These are indeed PivotFields (I was just using a simplified example in my original post). I have not had a chance to return to this project, yet. I will work on using your suggestions over the next few days, and update the thread then. Thank you!

 

[mgmirvine]

All,

Sorry for the long delay in replying.

JS411, your solution worked perfectly!

I truly thank everyone's help and advice.