Working with Page Breaks in Excel & VBA

Any time you print a spreadsheet you should review and edit the Page Breaks before printing.

Page Break View Mode

Because page breaks are not shown by default in Excel, you will want to switch to Page Break Preview view mode in order to work effectively with them.

To switch from Normal View to Page Break Preview, activate the View Ribbon Menu. Then select Page Break Preview (ALT > W > I).

Below is an example of what the view looks like. A dashed line represents an automatic page break whereas solid lines stand for manual page breaks.

Tip: To go back to Normal View, go to View Ribbon > Normal (directly next to Page Break Preview) (ALT > W > L).

<!–Ads3–>

Insert Page Breaks

To insert a row (horizontal) page break, highlight the row directly below where you want to place the break. Then navigate to the Layout Ribbon Menu and select Breaks > Insert Page Break (ALT > P > B > I).

Result:

Tip: The SHIFT + SPACE shortcut allows you to conveniently select an entire row.

Follow the same steps to insert a column (vertical) Page Break. Select the column directly to the right of where you want to place your break. Then go to Page Layout > Breaks > Insert Page Break (ALT > P > B > I).

Tip: The CTRL + SPACE shortcut allows you to conveniently select an entire column.

Delete Page Break

To delete page breaks you can use almost exact same steps as if you were to insert one. Select the row below or column to the right of the page break and perform these steps:

Under the Page Layout Ribbon Menu, select Breaks then Remove Page Break (ALT > P > B > R)

Note: Automatic page breaks cannot be deleted

Move Page Breaks

If you happen to mess up where you placed your breaks, don’t fret. You can simply move them to the desired location.

Activate Page Break Preview (ALT > W > I), and simply hover over any page break lines until the <-> shows up then drag it to the desired location.

Warning: If you happen to move an automatic page break, it will turn into its manual counterpart.

<!–Ads1–>

Reset Page Breaks

Sometimes, you want to reset all of the Page Breaks in a worksheet..

On the ribbon, head back to where you would insert a page break and select the reset option (ALT > P > B > A)

Hide Page Breaks in Normal View

Page breaks may appear in Normal View Mode; they can be useful or an eye sore. To hide them, follow these steps:

Click the File tab on the ribbon then select Options on the left menu:

Then navigate to the Advanced subtab in the left menu. Scroll down to Display options for this worksheet and unselect Show Page Breaks.

<!–Ads2–>

Page Breaks – VBA

Everything mentioned above can also be accomplished using VBA using these code examples:

‘Add Row Page Break
Worksheets("Sheet1").Rows(40).PageBreak = xlPageBreakManual

‘Add Column Page Break
Worksheets("Sheet1").Columns("Z").PageBreak = xlPageBreakManual

‘Clear Row Page Break
Worksheets("Sheet1").Rows(40).PageBreak = xlPageBreakNone 

‘Clear Column Page Break’
Worksheets("Sheet1").Columns("Z").PageBreak = xlPageBreakNone

‘Set Activesheet to Page Break Preview Mode
ActiveWindow.View = xlPageBreakPreview

‘Restore Activesheet to Normal View Mode
ActiveWindow.View = xlNormalView