Class HPageBreak (Excel VBA)

The class HPageBreak represents a horizontal page break.

For Each

Here is an example of processing the HPageBreak items in a collection.

Dim shts As Sheets: Set shts = 
Dim hpgbr As HPageBreak
For Each hpgbr In shts.HPageBreaks
	With hpgbr
	End With
Next hpgbr


Deletes the object.

Dim hpgbr As HPageBreak: Set hpgbr = 


Drags a page break out of the print area.

This method exists primarily for the macro recorder. You can use the Delete method to delete a page break in Visual Basic.

DragOff (Direction, RegionIndex)

Dim lngRegionIndex As Long: lngRegionIndex = 
Dim hpgbr As HPageBreak: Set hpgbr = 
hpgbr.DragOff Direction:=xlDown, RegionIndex:=lngRegionIndex


The following arguments are required:

Direction (XlDirection) - The direction in which the page break is dragged.

Possible Values are

xlDown Down.
xlToLeft To left.
xlToRight To right.
xlUp Up.

RegionIndex (Long) - The print-area region index for the page break (the region where the mouse pointer is located when the mouse button is pressed if the user drags the page break). If the print area is contiguous, there's only one print region. If the print area is discontiguous, there's more than one print region.


Returns the type of the specified page break: full-screen or only within a print area. Can be either of the following XlPageBreakExtent constants: xlPageBreakFull or xlPageBreakPartial.

xlPageBreakFull - Full screen, xlPageBreakPartial - Only within print area.
Dim hpgbr As HPageBreak: Set hpgbr = 
Dim xpbExtent As XlPageBreakExtent
xpbExtent = hpgbr.Extent


Dim hpgbr As HPageBreak: Set hpgbr = 
hpgbr.Location =


Returns or sets an XlPageBreak value that represents the page break type.

xlPageBreakAutomatic - Excel will automatically add page breaks, xlPageBreakManual - Page breaks are manually inserted, xlPageBreakNone - Page breaks are not inserted on the worksheet.
Dim hpgbr As HPageBreak: Set hpgbr = 
hpgbr.Type = xlPageBreakAutomatic


Adds a horizontal page break.

Add (Before)

Before: A Range object. The range above which the new page break will be added.

With Worksheets(1) 
 .HPageBreaks.Add .Range("F25") 
 .VPageBreaks.Add .Range("F25") 
End With


Returns a Long value that represents the number of objects in the collection.

Dim hpbs As HPageBreaks: Set hpbs = 
Dim lngCount As Long
lngCount = hpbs.Count


Returns a single object from a collection.

Item (Index)

Index: The index number of the object.

Dim hpbs As HPageBreaks: Set hpbs = 
Dim hpgbrItem As HPageBreak
Set hpgbrItem = hpbs(Index:=1)