Macro to insert rows before Page Breaks

[redjay]

Hi all

I am using the subtotal function to insert page breaks.

I would like some code which will search for the page breaks in my worksheet and automatically insert 2 blank rows above each Page Break.

Might this be possible?

Many thanks

 

[RolfJ]

This macro should do the trick:

Option Explicit
Sub InsertRowsBeforePageBreak()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    Dim nRowsAdded As Long
    Dim i As Long
    For i = 1 To sh.HPageBreaks.Count
        Rows(Range(sh.HPageBreaks(i).Location).Row + nRowsAdded & ":" & Range(sh.HPageBreaks(i).Location).Row + nRowsAdded + 1).Insert Shift:=xlDown
        nRowsAdded = nRowsAdded + 2
    Next i
End Sub

Hope this helped,
Rolf