Add or delete rows from a selection

So the problem you mention is why I never make my pivot tables stack on top of each other, always side-by-side, unless the number of rows is static (like 1 hour intervals in a day). But, if you have to have them on top of each other, then hide the rows instead of inserting and deleting them. So, to start, put a stupid number of blank rows in between them, something that the top pivot will never fill, say maybe like 200 rows, so that the bottom Pivot begins on row 250. Then a piece of code like this should work:

Please Login or Register  to view this content.

Hahaha apologies for the dutch in there

I have thought about this too but since pivot tables from the left to the right may also vary in length, hiding rows was not an adequate solution.
Regardless, I will try to work out your solution a bit further and see if i can make it work.

Thanks for the input!

You can also search for the last row in each pivot using that Eindtotaal. Then compare to see which row is further down and use that as lrow to build the string of what to hide.

What I did right now is work with the offset formula as i mentioned earlier:

=match("Grand total";Dashboard2!Q:Q;0)+1 --> name value "CI_Start"
=match("End critical";Dashboard2!Q:Q;0)-1 --> name value "CI_End"

=offset(Dashboard2!$P$1;CI_Start;0;CI_End-CI_Start;4) --> name range "Selection"

Then, in VBA:

Range("Selection").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove <<x50>>

Then, to prune back excessive rows, I created something similar to this but in reverse