Excel VBA - Hide range of rows after variable sized pivot table

Question

I trying to write a macro to format a number of sheets containing pivot tables. I am stuck on this one problem.

My sheet contains a number of stacked pivot tables. I need the VBA code snippet to hide, say, 1000 rows after the first table (or all tables except the top one if possible). The top table will vary in size from one day to the next so it is not possible to just hide a set range.

Thanks

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-04-04T13:59:52

To hide all the pivot tables except one, you may try something like this... In the below code, change the name of the pivot table you don't want to hide.

Sub HideAllPivotTablesButOne()
Dim ws As Worksheet
Dim pt As PivotTable
Application.ScreenUpdating = False
Set ws = ActiveSheet
lr = ws.UsedRange.Rows.Count
For Each pt In ws.PivotTables
    If pt.Name <> "PivotTable1" Then     'Name of the Pivot Table which you don't want to hide
        pt.TableRange2.EntireRow.Hidden = True
    End If
Next pt
Application.ScreenUpdating = True
End Sub

If you want to show all the pivot tables again, you may try the below code...

Sub ShowAllPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
Application.ScreenUpdating = False
Set ws = ActiveSheet
lr = ws.UsedRange.Rows.Count
For Each pt In ws.PivotTables
    pt.TableRange2.EntireRow.Hidden = False
Next pt
Application.ScreenUpdating = True

End Sub

Excel VBA - Hide range of rows after variable sized pivot table

1 Answers