How to distribute automatic page breaks between hard page breaks?

[JacekKotowski]

I am lookign for a sort of Orphan/Widow print control in Excel. I am printing long tables with totals for groups. (The table is a result of an sql query and not a subtotal function in Excel/) I managed to implement a procedure to recognize the text "Total" in column C and insert a HPageBreak right below:
<code>Dim cellTotal As Excel.Range

ActiveSheet.ResetAllPageBreaks
For Each cellTotal In Range("C6", Range("C65000").End(xlUp))
If InStr(cellTotal.Value, "Total") > 0 And Not InStr(cellTotal(3, 1).Value, "Grand Total") > 0 And Not cellTotal.Value = "Grand Total" Then
ActiveSheet.HPageBreaks.Add (cellTotal(3, 1))
End If


Next cellTotal</code>

However, sometimes Excel inserts an automatic pagebreak (marked with blue dashed line). Oftentimes, such an automatic pagebreak falls too close to the hard pagebreak inserted by a macro . Is it somehow possible to rerun the above macro to check if automatic page breaks appear to close to the hard breaks and move them away, eg. ca 3 lines upwards from the pagebreak introduced by the VBA procedure, so that pages do not print with Total only or 1 -two rows with a Total:


In brief, I am thinking of a macro that does: "If automatic pagebreak closer than three lines to manual pagebreak then move the automatic pagebreak 3 or x rows higher than the hard pagebreak placed by the vba macro."


I am thinking of a simple procedure that would go again through the range affected by the above macro and move the automatic page breaks away, upwards from VBA introduced hard pagebreaks if they are too close.

Unfortunately I do not know how to chceck with vba if an automatic pagebreak is inserted by Excel and how to move it away.
Thank you in advance for any indication how to move forward.