Programming Excel with VBA and .NET

Control Paging

Use the HPageBreaks and VPageBreaks collections to add manual page breaks to a worksheet in code. For example, this code adds horizontal page breaks to a worksheet every specified number of rows:

Sub AddHBreaks(rows As Integer)
    Dim ws As Worksheet, hpb As HPageBreak, i As Integer
    Set ws = ActiveSheet
    For i = rows To ws.UsedRange.rows.Count Step rows
        ws.HPageBreaks.Add ws.rows(i)
    Next
End Sub

Use the HPageBreak and VPageBreak objects’ Delete method to remove individual page breaks or use the Worksheet object’s ResetAllPageBreaks method to remove all manual page breaks as shown here:

Sub RemoveBreaks(  )
    ActiveSheet.ResetAllPageBreaks
End Sub

The page break collections contain only manual page breaks. Even though there is a Type property that suggests you might be able to get automatic page breaks, you can’t. That means the Count properties of the collections return only the number of manual page breaks. For example, this code displays the page count of a worksheet that contains only manual page breaks:

Sub ShowPageCount(  )
    Dim ws As Worksheet, hb As Integer, vb As Integer
    Set ws = ActiveSheet
    hb = ws.HPageBreaks.Count + 1
    vb = ws.VPageBreaks.Count
    If vb = 0 Then vb = 1
    MsgBox "This worksheet has " & hb * vb & " pages."
End Sub

The only way to control automatic page breaks is to change the page margins using the PageSettings object.