VBA to stop automatic page breaks from occurring in a range of 4 rows

I need to stop page breaks from occurring within a group of 4 rows, but do not want them to always occur before them. This is a signature line at the end of a report and we would like the signatures to stay together. As the row number changes with the amount of data before it, I have selected the group of cells in question and titled them “Signature”. Currently there are no manual page breaks on the worksheet. I have added the code that I am using to format the document and print in case it helps. Thanks in advance!

Please Login or Register  to view this content.

Hi there,

Define your signature block as a Named Range called "tblSignatureBlock", and see if the following code does what you need:

It uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements.

The code should be inserted in the ThisWorkbook VBA CodeModule of your workbook, not in a standard VBA CodeModule.


The code works by checking whether or not an automatic PageBreak occurs within the Signature Block, and if so, inserts a temporary manual PageBreak immediately before the Signature Block, performs the Print operation, and then removes the temporary PageBreak. Inserting and then removing the temporary PageBreak are ignored as "changes" as far as the "Saved" state of the workbook is concerned.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks for your help, the time and the writeup on how it works! This works great!

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated

You're very welcome - glad I was able to help.

Best regards,

Greg M