auto page break around cell range so as not to print a range across 2 pages

[lonemascot]

Hi everyone.

I have a sheet which I need to print out. Its contents is made up of about 30 different sections which change (by hiding cells with zero in them) so its length can increase or decrease depending on content. Normally the finished sheet prints out over 10 pages or so and so far I have been having to set page breaks manually (in page view) so that it prints correctly. Some of these sections are small enough to fit several on one page so the page break at SubTotal change is no good to me as I'll end up with 30 pages and I don't want to unnecessarily waste paper, save the planet and all that.

What I need to happen is for page breaks to occur every 27cm unless the proposed break is in the middle of a cell range and in that case should break just before that range (all my ranges start with the word "Range_"blah blah etc). I don't know if its possible to specify distance in cm but the problem with using row numbers is that some of my cells have wrapped text and can be as deep as 3 or 4 rows.

None of my current sections (at the moment) are over a page long but if they was then obviously they would have to split over 2 pages.

so in a non VBA language I reckon it'll go something like this:

insert page break every 27cm
else, check if in middle of range and if so break before "Range_"
else, if range is longer than 27cm break at 27cm

I'd appreciate any help with this as I've been scouring the internet for ages and I can't find anything that works.
See attached file for rough idea of what I've got to deal with.