Excel 2010- How to have additonal rows show below pivot table

Mark-Heinze
Mark-Heinze used Ask the Experts™
on
How do you get additional rows to show right below the table no matter how collapsed or expanded it is?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

you could use a named range and a worksheet change event.

Create a range name called "theGap" that is defined by a formula along these lines:

=Sheet2!$A$9:INDEX(Sheet2!$A:$A,MATCH("Grand Total",Sheet2!$A:$A,0)+1)

The static text is in A10. The last row of the pivot table shows the label "Grand Total". Adjust these to your situation.

Then use this macro in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.EntireRow.Hidden = False
    Range("theGap").EntireRow.Hidden = True
End Sub

Open in new window


cheers, teylyn
Most Valuable Expert 2012
Top Expert 2012

Commented:
VERY Nice!

Not for points.  You might turn screen updating off then back on at the beginning, then end of the routine, respectively - otherwise the page would flicker with each sheet selection change..

Dave
Most Valuable Expert 2011
Awarded 2010
Commented:
Thanks for that, Dave.

For completeness' sake, here is the code with the screen flicker turned off:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Application.ScreenUpdating = False
    Cells.EntireRow.Hidden = False
    Range("theGap").EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub
          

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
Also, NFP - Sorry - I meant to also add the comment, you might also try:

Worksheet_PivotTableUpdate() event as an alternative.

Cheers,

Dave
Most Valuable Expert 2011
Awarded 2010

Commented:
Here is a working sample. You need to start with the Pivot Table fully expanded, then add the static text below it. Then create the range name and add the macro.

cheers, teylyn
HideRowsBelowPivotTable.xlsm

Author

Commented:
Thank you both - that was exactly what we were looking for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial