Remove print preview lines (Page Breaks)
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 that will be printed on a single paper or pdf.
What's on this page
- How to delete print preview lines [Excel 2010-2016]
- How to remove print preview lines using Immediate window
- How to delete print preview lines [Excel 2007]
- How to delete print preview lines [Excel 2003]
- How to delete print preview lines [Mac Excel 2011]
- How to remove Print preview lines programmatically
[VBA]
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. See detailed instructions below.
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.
The is also a macro-enabled workbook for you to download further down in this post.
It seems that if you close the workbook and then open it again the print preview lines disappear, however, this is in my opinion not recommended.
How to remove dotted lines (Excel Options)
- Click on tab "File" on the ribbon.
- Click on "Options"
- Click on tab "Advanced".
- Scroll down to "Display Options for this worksheet".
- Disable "Show Page Breaks".
- Click "OK" button.
How to remove dotted lines (Immediate window)
If you are familiar with the Immediate window in the Visual Basic Editor you can probably more quickly delete the print preview lines than through Excel Options.
- Copy code below.
- Press Alt+F11 to open the VB Editor.
- Paste code to the Immediate window
- Press Enter
- Return to Excel
ActiveSheet.DisplayPageBreaks = FalseHow to remove print preview dotted lines [Excel 2007]
- Click the Office button located at the top left side of your screen.
- Click "Excel Options".
- Click tab "Advanced" in the left window.
- Find "Display Options for this Worksheet".
- Disable the check box "Show Page Breaks"
- Click "OK" button.
How to remove print preview dotted lines [Excel 2003]
- Go to the Tools menu.
- Click Options.
- Click the View tab.
- Disable checkbox Page Breaks found in Windows Options.
- Click OK button.
Excel 2011 Macintosh
- Go to "Preferences" on the menu.
- Click View in "Authoring".
- Disable checkbox "Show Page Breaks" located below "Windows Options"
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 useful macros in your personal macro workbook and become a lot more efficient in your work.
Macro to disable print preview lines
What happens if we record a macro while disabling "Page Breaks" in Excel options? This is what the macro recorder returns:
Macro1() ActiveSheet.DisplayPageBreaks = False End SubUse the above line in the examples below if you don't want to toggle print preview lines.
Macro toggles print preview lines on the active worksheet
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 SubMacro toggles print preview lines on every sheet in the workbook
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 SubMacro toggles print preview lines on every sheet in all open workbooks
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 SubWatch a video where I explain how to use the macros above
Where to put the code
- Copy the preferred macro above.
- Go to Visual Basic Editor (VBE). (Alt + F11)
- Click "Insert" on the menu.
- 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
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
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 quite useful. Allow me to explain, sometimes you need to take a screenshot of your worksheet […]
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. […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Use the img tag, like this: <img src="Insert pic link here">