Dynamic Page Breaks

[RyanChristy]

Hello!

I have a form which can range between 4 to 7 pages in length. The form is broken down into main sections, with multiple rows within those main sections.
The height of the rows can change dramatically depending on the amount of text in some cells - hence the 4 to 7 pages long. What I want is to set page breaks so that the main sections are never broken. I can set the manual page breaks to do this, but I end up with a lot of blank space on the print outs and the document increases in length. I would like it to be as efficient as possible.

Can anyone help?

Many Thanks

Ryan

 

[Sthrncali]

You can write a macro to set the page breaks in real time..

Here is the simple code to add a horizontal page break, at a given cell location.

Sheet1.HPageBreaks.Add Before:=ActiveCell

So all you would need to do is have your code clear all page breaks, then cycle through your form and manually at page breaks based on whatever logic you add to identify what row should contain the page break.

 

[RyanChristy]

I apologise for my stupidity but I dont think that would help. I can manually change the page breaks before each print out without vba but I'm trying to avoid that.

For example if the form were to have 10 rows, depending on the heights of those rows the form can be 3 or 5 pages.
If I don't set page break the form prints in a continuous manner, however there are breaks in the form where I don't want them.
I want excel to recognise that I don't want rows 2 and 3 to be on seperate pages, or 5,6 and 7 to be on different pages.
As the heights of the rows can change a lot I want it to print using the least amount of pages and with minimal empty white space.

My actual form is 454 rows long, with multiple sections that I don't want broken.

 

[Sthrncali]

Right... The only way to accomplish this is via VBA utlizing the code I provided... What you would need to add to make it functional for your purpose would be 2 things:

1. You will need some sort of "Marker" or Logic that tells the code when a new page break is appropriate - in a position that would prevent your rows from being split in two pages.

2. You will need to add a LOOP to cycle through all the rows in your form and repeatedly add your page breaks at the "Markers" until your code has cycled through all potential rows.

With regards to #1 above, given that the row height's are fluctuating, you would likely need to utilize the "row.height" property to accomplish your task...

I.E. Identify a maximum number of pixels to put on each page, then cycle through the rows in your form and SUM the height property of each row - adding a page break every X pixels

 

[RyanChristy]

Ahhhh I understand now. I think I was just being stupid.
Thank you very much for your help - I'll post to let you know if I get it working!