Link to home
Start Free TrialLog in
Avatar of Mark-Heinze
Mark-Heinze

asked on

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

How do you get additional rows to show right below the table no matter how collapsed or expanded it is?  Thanks.
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

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
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
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, NFP - Sorry - I meant to also add the comment, you might also try:

Worksheet_PivotTableUpdate() event as an alternative.

Cheers,

Dave
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
Avatar of Mark-Heinze
Mark-Heinze

ASKER

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