query auto page breaks

[rerickson]

I need to determine where Excel will put automatic page breaks in a sheet where the #rows, and their heights, vary. Reading .PageBreak for every row turns out to be very slow! Is there a better way?

My sheets have several sections; we'd like to avoid page breaks within a section. Rows get deleted or hidden by macro, based on the data, when finalizing a sheet; I’ll set row.PageBreak =xlManual where a section would otherwise spill past the end of the page. (In Word this would be so easy...)

One solution is to scan through the rows, looking for .PageBreak = xlAutomatic. Surprisingly, this takes about 50 to 100ms per row! (Apparently Excel figures out the page layout with each fetch of the PageBreak property; ascending and descending order are equally slow.)

What to do instead? (Alas, sheet.VPageBreaks has only manual breaks, not the automatic ones.)

Alternatively, might it be possible to determine how far down the printed page a row will land? I could then compare that_position + range.RowHeight to PaperSize - margins.

This is with Office 2013.
Thanks!

 

[rerickson]

Researching further, it seems that .VPageBreaks should contain both manual and automatic page breaks (and thus would be an efficient approach here), but that collection is not always populated, and it's not clear what will populate it! Some articles say you need to select the last cell, or scroll that cell into the window, or enter Print Preview mode; all those still leave me with ActiveSheet.VPageBreaks.Count =0.