I have a spreadsheet where the program is designed to hide unused rows
and columnes. Therefore each time it is used, it ends up to be a
different size before printing. I want to print the spreadsheet full
width, and I discovered I could do that by blanking the number of
pages.

My problem is that I want to make page breaks at a logical spot where
there are sub-headings. This spot will differ each time (there are 8
subheadings). I would like to always make the page break before a sub-
heading. Since the rate of reduction varies each time, and the number
of rows and columns hidden in each group varies each time the program
is used, I do not know how to make this happen.

My goal would be to say, for example, if the automatic page break
falls between rows 105 and 150, then I would like to force a page
break at row 98. Then recheck and find the next automaticlly
calculated break after the maual break is inserted. if the next
automatically calculated page break falls between rows 170 and 230, I
would like to force a page break at row 165, etc. the only constants
are the 8 places where a page break would logically be made.

I have tried to identify where the automatic page breaks are, with the
goal of using that information to develop a formula in a macro to set
the new page breaks, but that has not worked. I found the following
in an earlier post and tried to use it to identify the row(s) where
the page breaks were. It worked once and then would not work again.
I am at a loss and my time window is running down to complete this
project.

Dim n As Variant
ActiveSheet.ResetAllPageBreaks

For n = 1 To ActiveSheet.HPageBreaks.Count

Debug.Print ActiveSheet.HPageBreaks(n).Location.Address
Range("F4").FormulaR1C1 = ActiveSheet.HPageBreaks
(n).Location.Row

Next

can anyone help me?