Changing the heights of pages based on dataset rows...

[Hyflex]

Hey,

I'm looking for some help on creating new pages based on the end of "datasets".

If you look at the example data, the green cells are always 5 rows but each dataset might have 1, 2, 3 or 4 green groups, so a potential of 20 rows.

I want to set the page heights based on these datasets, so in this example the end of page is by default at row 50 / row 51 which is in the middle of a dataset.

I need to move the page end to the end of the dataset + 1 so the page end would be on row 55 / row 56.
However if the end of page was on under row 43 (under half the total size of the dataset) then the new end of page would be moved up to row 32/row 33)

Thanks in advance
- Hyflex

[ragulduy]

Something to start with, there is probably an easier/quicker method than using the loops, but to get it to work for your example with the merged cells and so on, depending on what your actual data looks like you could probably rewrite it:

Please Login or Register  to view this content.

[Hyflex]

Ahh,

I couldn't find this "HPageBreaks" function anywhere, it works pretty good but after it's done the last one it errors out with a subscript error.

If you try this example, you'll see it goes wrong.

Anyone know how to fix that?

[ragulduy]

Not sure what is calling it but you could fix it with some error handling:

Please Login or Register  to view this content.

[Hyflex]

Hey yudlugar,

Thank you ever so much, It works good now however I'm trying to incorporate this with saving to PDF and have found a few new problems.

1) Optimal page length is 90~ rows so the page length should be as close to 90 rows as possible but using the same dataset protection as used previously.
2) I am wanting to insert a "page count" up in A2 something like "3 Page(s)"
3) Is there a way to pre-set the zoom level or remove whitespace boarders automatically with excels add-in for saving as pdfs as currently I have huge amounts of white space around my saved content...

Does anyone know how to do this?

Thanks in advance
- Hyflex

[Hyflex]

Anyone have any ideas? :/

Thanks

[ragulduy]

1. Just adjust the macro above to set place it a 90 row spacing first and then move it up or down:

Please Login or Register  to view this content.

2. Easiest thing to do would be to just a header or footer to display total number of pages. I don't think there is a formula that will return page numbers to put it into a cell so if you really need it in A2 you would need a formula to work out how many pages you had. (i.e. find last row and divide by 90 or something)

3. I don't understand what you mean by this - there is a zoom level in the page setup which you can set automatically but if you are manually adding and setting pagebreaks this will kind of be overwritten. You can set the print area automatically using a macro which might be what you mean? In the example you had it was just a single column which will always look a little strange when printed. You could try changing the width of the column whilst printing I guess.