Counting lines per page

Have you tried clearing the pages(s) tall box (highlight the number in it and press Delete)? Then you won't need to insert any page breaks.

Not following you Andrew.

If I clear the tall box then I may get infinite pages but that's not the problem.

If on one printer the print area is too wide for and A4 page then fitting to 1 wide and 10 tall will make it fit but the lines per page will change. That means that the when I expect the page to be eg. 21 lines it may change to 22 so my VBA needs to know that there are now 22 lines when calculating page breaks.

eg.

Tool:
a
b
c

Nut:
a
b
c

If the number of lines on my printer is 9 then all of the above will be on page 1 but on the other printer there are only 8 so I need a page break on line 6 (ie Nut.

So, isn't there a worksheet.page(1).linecount or something ?

If the print area starts at row 1 and there are only automatic horizontal page breaks:

MsgBox ActiveSheet.HPageBreaks(1).Location.Row - 1

will return the number of rows per printed page.

ActiveSheet.HPageBreaks(1).Location.Row - 1

works well when there are more than one page to print but when there is less than one page it is "Subscript out of range"

The reason why I need to now how many lines fit on a page is because there are so many different printers in the office and each one has a different page length. I want to format the page so the page breaks are between titles. As soon as I add page breaks, the "Fit to" stops working so I need another solution.

Any other idea??

OK, here's my latest bandaid until someone can suggest otherwise.

I have added a worksheet with items in column A, lines 1 to 500

ActiveSheet.HPageBreaks(1).Location.Row - 1 works with this static sheet so I will use it to get around the "Subscript out of range" error.

I would still appreciate any more ideas.

Thanks