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.
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.