CODEWITHSUNDEEP
vbaexcel
Jackson
Jackson

Reputation: 225

In Excel VBA, how do you remove grey page break lines?

Question:

How do you remove grey dotted line page breaks (not the blue ones) from an Excel sheet using VBA code?

Background:

We have a collection of ODBC spreadsheets generated via VBA. When running two different (though similar) spreadsheets on my local box without manually changing any options in Excel, one shows dotted grey lines on the page breaks (see image below) and one does not. Both are large enough to extend past one page. I have not been able to find a difference in the code that gives reason to this.

Most of the advice on the internet points toward the options in Excel via the GUI, which is not an option. I am seeking a VBA solution.

What I've tried:

I've tried several different variations and applications of DisplayPageBreaks = False. Either I'm using it incorrectly, or it is not the correct setting. My most reasonable use of this property has been:

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False

Note that the variable str_Worksheet_Name works in many other lines in the code, example: ActiveWorkbook.Worksheets(str_Worksheet_Name).Cells(1, 1).Select correctly selects cell A1 on the desired worksheet.

Reference Image:

These are the lines I am trying to remove:

enter image description here

Thanks in advance.

Additional Info:

It sounds like something in this code snippet is preventing them from showing up on this spreadsheet. Note that this snippet is from the sheet that correctly does not show the page breaks:

Public Function cmlapi_Set_To_Landscape()

On Error Resume Next
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
End With
If glob_Header_On_Every_Page = True Then
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$" & HEADER_OFFSET
    End With
End If
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

End Function

No luck with this. The lines are still there. Note that the .Orientation = xlLandscape did work, so there shouldn't be a concern of where this is specified in the code.

ActiveSheet.DisplayPageBreaks works in the immediate window (Ctrl + G) for the spreadsheet after it has been run. Rerunning puts the dotted lines back again. Oddly using this same property in the code doesn't seem to have an effect, but clearly is the correct code. Perhaps there's a specific place it needs to be? I would have thought as long as I either use ActiveSheet when the correct sheet is selected or specified my sheet name it shouldn't really matter.

It was further suggested that I try it on the last line. Good idea, but didn't work out either. Tried both of these on the last line in two separate trials:

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False ActiveSheet.DisplayPageBreaks = False

Solution: See Mat's Mug's answer. FitToPagesTall = 1 and FitToPagesWide = 1 worked after also setting Zoom = False. I'll shamefully admit I'm rather surprised I didn't find this earlier as it is exactly stated as such on Microsoft's Dev Network site:

Link: MSDN Info

Code:

 .Zoom = False 
 .FitToPagesTall = 1 
 .FitToPagesWide = 1

Not sure why the Zoom line is needed, but that was the missing piece.

Thanks to all who helped!

Upvotes: 2

Views: 2275

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Try removing any Zoom, and then scaling the PageSetup to fit a single page:

ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.FitToPagesWide = 1

That should get the dotted lines out of the way.

Upvotes: 4

Vityata
Vityata

Reputation: 43575

Write the following in the immediate window and press Enter:

ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks

Or simply write Or simpy write - ActiveSheet.DisplayPageBreaks = False in VBA, at the end of your code.

Worksheet.DisplayPageBreaks Property (Excel)

Upvotes: 2

D_Bester
D_Bester

Reputation: 5911

The dotted lines are simply showing where the page will break. Has nothing to do with inserted page breaks. So removing page breaks doesn't do anything. The page will still break somewhere unless you specify one page wide/tall per Mats Mug.

If you don't want to see the dotted lines, just save, close and reopen. The lines are gone. Of course they will reappear when you preview or print.

It's a feature, not a flaw.

Upvotes: 2

Related Questions