Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other words they show how the worksheet will be printed.
If you click Page Break Preview button on tab "View" and then click "Normal" button, the lines appear. So how do you get rid of them?
You can´t easily disable them clicking a button on the ribbon, you must go to Excel Options.
- Go to tab "File" on the ribbon
- Click "Options"
- Click "Advanced"
- Scroll down to "Display options for this worksheet"
- Disable "Show page breaks"
- Click OK
The VBA solution
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