Hide/unhide blank rows between multiples worksheet

Sian1

Board Regular
Joined
Nov 9, 2009
Messages
57
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Nov 9, 2009
Messages
57
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Glad it worked for you. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,166
Messages
5,623,119
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top