Hi Azuki,
This code would hide all rows between the two pivots (replace with the names of your pivots).
Code:
Sub HideRowsBtwnPivots()
'--Hides all rows between two PivotTables.
Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
'---Get last row of top PivotTable
With ActiveSheet.PivotTables("PivotTable1").TableRange2
lPivot1LastRow = .Row + .Rows.Count - 1
End With
'---Get first row of bottom PivotTable
With ActiveSheet.PivotTables("PivotTable2").TableRange2
lPivot2FirstRow = .Row
End With
'--Hide rows in-between
If lPivot1LastRow + 1 < lPivot2FirstRow Then _
Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = True
End Sub
If those Pivots are stacked in the same columns, you will probably want to leave at least one row between.
Here is some code to let you specifiy a variable number of rows.
Code:
Sub HideAllButXRowsBtwnPivots()
'--Hides blank rows between two PivotTables.
Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
Const lRowsBtwn As Long = 12
'---Get last row of top PivotTable
With ActiveSheet.PivotTables("PivotTable1").TableRange2
lPivot1LastRow = .Row + .Rows.Count - 1
End With
'---Get first row of bottom PivotTable
With ActiveSheet.PivotTables("PivotTable2").TableRange2
lPivot2FirstRow = .Row
End With
'--UnHide rows in-between
If lPivot1LastRow + 1 < lPivot2FirstRow Then _
Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = False
'--Hide all but specified number of rows in-between
If lPivot1LastRow + 1 + lRowsBtwn < lPivot2FirstRow Then
Rows(lPivot1LastRow + 1 + lRowsBtwn & _
":" & lPivot2FirstRow - 1).Hidden = True
End If
End Sub