Hide/unhide blank rows between multiples worksheet

[Sian1]

Hi. How can I hide/unhide blanks rows between pivot tables. I have multiple pivot tables with slicer however it might have big gaps between based on the criteria chosen with slicers. Let's say I have pivot A taking row b2:h70 pivot b b73:h130 and pivot c b132:h180 when all data returns. Let's say, I selected a criteria and pivot a only return values taking b2:h40 and pivot b b73:h60 and pivot c b132:h120.; this create huge gaps between pivot a and b and between b and c.

Thanks in advance.

 

[Peter_SSs]

See if this does what you want but test with a copy of your workbook.
To implement ..
1. Right click the Pivot Table sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing Slicer values.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

The code is set to leave 2 blank rows between each Pivot Table but that can be adjusted in the 'Const' line near the start of the code.
I have assumed that there are no blank cells in column B within the pivot tables themselves.
The code should run and readjust the spacer rows whenever a PT on the sheet is updated, including when slicer values are chosen/altered.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Dim rA As Range
  
  Const RowsToLeave As Long = 2 '<- Edits as required

  Application.ScreenUpdating = False
  Cells.EntireRow.Hidden = False
  For Each rA In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
    If rA.Rows.Count > RowsToLeave Then rA.Resize(rA.Rows.Count - RowsToLeave).EntireRow.Hidden = True
  Next rA
  Application.ScreenUpdating = True
End Sub

 

[Sian1]

See if this does what you want but test with a copy of your workbook.
To implement ..
1. Right click the Pivot Table sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing Slicer values.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

The code is set to leave 2 blank rows between each Pivot Table but that can be adjusted in the 'Const' line near the start of the code.
I have assumed that there are no blank cells in column B within the pivot tables themselves.
The code should run and readjust the spacer rows whenever a PT on the sheet is updated, including when slicer values are chosen/altered.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Dim rA As Range
 
  Const RowsToLeave As Long = 2 '<- Edits as required

  Application.ScreenUpdating = False
  Cells.EntireRow.Hidden = False
  For Each rA In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
    If rA.Rows.Count > RowsToLeave Then rA.Resize(rA.Rows.Count - RowsToLeave).EntireRow.Hidden = True
  Next rA
  Application.ScreenUpdating = True
End Sub

Brilliant!!

thanks so much

 

[Peter_SSs]

You are welcome. Glad it worked for you. Thanks for the follow-up.