VBA Page Break Issue

[nikki333]

Hi Folks

I'm trying to write code to put manual page breaks on a report. The report contains several blocks of rows that I don't want to break across pages.

So, I check if the automatic page break falls in the middle of one of these blocks, and set a manual break before that block. This works fine while the report has only 1 pagebreak.

However, in case of more than 2 pages, I'd like to find the automatic page break for the second page and so forth, after I set the line number of the page break of the previous page.

For example:

i get the number of pages like so: numSeitenzahl = ExecuteExcel4Macro("Get.Document(50)")
and then get the line number for the automatic break like so: numZeileUmbruch = .HPageBreaks.Item(i).Location.Row

As said, this works for the first break, but then I need a way to tell excel that the first break has been changed and that it should look for a reasonable 2nd break. Any ideas?

[Paul_Hossler]

I think the HPageBreaks collection has the information you need, and Excel keeps it updated as you manually insert page breaks




Option Explicit
Sub test()
Dim oPageBreak As HPageBreak

ActiveSheet.ResetAllPageBreaks


For Each oPageBreak In ActiveSheet.HPageBreaks
Debug.Print "Before Add -- " & oPageBreak.Location.Row
Next

'second pass, insert break so that row 70 is on new page
ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Rows(70)


For Each oPageBreak In ActiveSheet.HPageBreaks
Debug.Print "After Add -- " & oPageBreak.Location.Row
Next
End Sub

[nikki333]

You are right. Thank you