Worksheet.HPageBreaks property (Excel)
Returns an HPageBreaks collection that represents the horizontal page breaks on the sheet. Read-only.
expression A variable that represents a Worksheet object.
There is a limit of 1,026 horizontal page breaks per sheet.
The following code example displays the number of full-screen and print-area horizontal page breaks.
For Each pb in Worksheets(1).HPageBreaks If pb.Extent = xlPageBreakFull Then cFull = cFull + 1 Else cPartial = cPartial + 1 End If Next MsgBox cFull & " full-screen page breaks, " & cPartial & _ " print-area page breaks"
Sample code provided by: Bill Jelen, MrExcel.com
The following code example adds a page break when the value of a cell in column A changes.
Sub AddPageBreaks() StartRow = 2 FinalRow = Range("A65536").End(xlUp).Row LastVal = Cells(StartRow, 1).Value For i = StartRow To FinalRow ThisVal = Cells(i, 1).Value If Not ThisVal = LastVal Then ActiveSheet.HPageBreaks.Add before:=Cells(i, 1) End If LastVal = ThisVal Next i End Sub
About the contributor
MVP Bill Jelen is the author of more than two dozen books about Microsoft Excel. He is a regular guest on TechTV with Leo Laporte and is the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.