Auto page setup/row height adjust

[alfgrey]

Hi, thanks in advance for any advice or assistance submitted.

So i have invoice, purchase order and quotation templates that i made some time ago. They all have some quirky little macros within (mostly from input from this forum) that really help speed up the filling out process of each document. The problem is i built these templates to stay within the custom margins of a single page, now things have changed since then an I'm constantly now needing a second page for extra line items of which each time i have to insert additional rows, make sure autosum for totals at thee bottom still work etc etc. I end up spending far too long tweaking the document so that it becomes printer friendly and its becoming a nuisance!

The data, including borders, font etc which sits within 4 adjacent rows must be at the bottom of the last page no matter how many pages in the document are eventually created. These rows/cells contain formulas so must still be able to calculate totals etc no matter where they sit in the document.

Our services can sometimes be quite bespoke and as such sometimes the cell in each row that contains the service description will often require 2, 3, 4 or maybe 5 lines of text. this then means the row heights need manually adjusting which then pushes my 4 rows of data at the bottom around and i have to spend ages readjusting the layout. is there a way 2 do this =If(text in column C, lines of text = 1 then row height = 19.50, lines of text =2 then row height = 32.75, lines of text =3 then row height =46.50 etc etc

I guess then any rows between the last line of inputted data and the bottom 4 formula rows that remain blank need to be automatically hidden to reduce the pages to the minimum required before finally placing the 4 rows of formulas at the bottom of the last page.

Hope this makes sense.

[nathansav]

Please Login or Register  to view this content.

This is what you'd do, have a look at some articles on google about looping ranges in VBA, this will give you what you want then. Then you can do your if statement.