Article updated on April 20, 2018

delete page break printer lines

Have you ever wondered how these lines got there on a worksheet?
They show where pages will break, in other words, they show how much of the worksheet will be printed on paper.

If you click Page Break Preview button on tab "View" and then click "Normal" button, the lines appear, however, they don't disappear automatically.

So how do you get rid of them?

You can't easily disable them clicking a button on the ribbon, unfortunately, you must go to the Excel Options and disable it there.

I made a macro, demonstrated later in this article, that you can use to quickly disable the print preview lines, put it in your personal toolbox and use it whenever necessary.

Instructions

  1. Click on tab "File" on the ribbon.
  2. Click on "Options"
  3. Click on tab "Advanced".
  4. Scroll down to "Display Options for this worksheet".
  5. Disable "Show Page Breaks".

Build a macro and automate these steps

If you often disable print preview lines manually why not build a macro that does it for you?

It is not hard, simply copy the macro and paste it to your workbook module. If you want to use it any workbook you open, put it in a personal macro workbook and link it to the Quick Access Toolbar or the ribbon.

In fact, you can save many useful macros in your personal macro workbook and become a lot more efficient in your work.

The VBA code needed

What happens if we record a macro while disabling "Page Breaks" in excel options?

This is what the macro recorder returns:

ActiveSheet.DisplayPageBreaks = False

Using that code you can now show or hide "Page Breaks" on the active sheet with a macro. Meaning, if "Page Breaks" are visible this macro hides them. If "Page Breaks" are hidden, the macro makes them visible.

Sub Macro1()
ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks
End Sub

The following macro shows or hides "Page Breaks" on every sheet in the current workbook.


Sub Macro2()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    sh.DisplayPageBreaks = Not sh.DisplayPageBreaks
Next sh
End Sub

This macro toggles "Page Breaks" on every sheet in every open workbook.

Sub Macro3()
Dim wb As Workbook
Dim sh As Worksheet
For Each wb In Workbooks
    For Each sh In wb.Worksheets
        sh.DisplayPageBreaks = Not sh.DisplayPageBreaks
    Next sh
Next wb
End Sub

Watch a video where I explain how to use the macros above

Where to put the code

  1. Copy the preferred macro above.
  2. Go to Visual Basic Editor (VBE). (Alt + F11)
  3. Click "Insert" on the menu.
  4. Insert a new module.

Don't forget to save the workbook as a macro enabled workbook  (*.xlsm file) or the macro is gone the next time you open the workbook.

Here is how to put this macro on the Excel ribbon:

Add your personal Excel Macros to the ribbon

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]

Download excel *.xlsm file

Toggle Page Break printer lines.xlsm

Recommended posts

Add your personal Excel Macros to the ribbon

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]

Repeat headers on every page you print

If you want each page to have the same row(s) or column(s) to be repeated at every page you print. […]

Print consecutive page numbers based on multiple worksheets

Instructions: Go to tab "Insert" on the ribbon Click "Header and Footer" button Click "Go to Footer" button Click "Page […]

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet. Then assign a macro to […]

Print screen the entire worksheet

I recently discovered something pretty cool. Sometimes you need to take a screenshot of your worksheet and you can do […]

Microsoft Office 2003/2007: How to turn on/off clipboard

The clipboard is a tool used for collecting things you have copied, for later use. But if you never use […]