title: In Excel VBA, how do you remove grey page break lines?
body: <p><strong>Question:</strong> </p>
  
  <p>How do you remove grey dotted line page breaks (not the blue ones) from an Excel sheet using VBA code?</p>
  
  <p><strong>Background:</strong> </p>
  
  <p>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 <em>(see image below)</em> 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.</p>
  
  <p>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.</p>
  
  <p><strong>What I've tried:</strong></p>
  
  <p>I've tried several different variations and applications of <code>DisplayPageBreaks = False</code>. Either I'm using it incorrectly, or it is not the correct setting. My most reasonable use of this property has been:</p>
  
  <p><code>ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False</code></p>
  
  <p>Note that the variable <code>str_Worksheet_Name</code> works in many other lines in the code, example: <code>ActiveWorkbook.Worksheets(str_Worksheet_Name).Cells(1, 1).Select</code> correctly selects cell <code>A1</code> on the desired worksheet.</p>
  
  <p><strong>Reference Image:</strong></p>
  
  <p>These are the lines I am trying to remove:</p>
  
  <p><a href="https://i.stack.imgur.com/5JofT.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5JofT.jpg" alt="enter image description here"></a></p>
  
  <p>Thanks in advance.</p>
  
  <hr>
  
  <p><strong>Additional Info:</strong></p>
  
  <ul>
  <li>RE Mat's Mug's comment:</li>
  </ul>
  
  <p>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 <strong>does not</strong> show the page breaks:</p>
  
  <pre><code>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:$" &amp; HEADER_OFFSET
      End With
  End If
  ActiveWindow.ScrollColumn = 2
  ActiveWindow.ScrollColumn = 1
  
  End Function
  </code></pre>
  
  <ul>
  <li><p>Per answer from Mat's Mug:</p>
  
  <pre><code>With ActiveSheet.PageSetup
      .Orientation = xlLandscape
      .FitToPagesTall = 1
      .FitToPagesWide = 1
  End With
  </code></pre></li>
  </ul>
  
  <p>No luck with this. The lines are still there. Note that the <code>.Orientation = xlLandscape</code> did work, so there shouldn't be a concern of where this is specified in the code.</p>
  
  <ul>
  <li>Per answer from Vidyata:</li>
  </ul>
  
  <p><code>ActiveSheet.DisplayPageBreaks</code> 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 <code>ActiveSheet</code> when the correct sheet is selected or specified my sheet name it shouldn't really matter.</p>
  
  <p>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:</p>
  
  <p><code>ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False</code>
  <code>ActiveSheet.DisplayPageBreaks = False</code></p>
  
  <ul>
  <li>I tried using the PageSetup code above. That didn't do it either.</li>
  </ul>
  
  <hr>
  
  <p><strong>Solution:</strong> See Mat's Mug's answer. <code>FitToPagesTall = 1</code> and <code>FitToPagesWide = 1</code> worked after also setting <code>Zoom = False</code>.  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:</p>
  
  <p>Link: <a href="https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/pagesetup-fittopageswide-property-excel" rel="nofollow noreferrer">MSDN Info</a></p>
  
  <p>Code:</p>
  
  <pre><code> .Zoom = False 
   .FitToPagesTall = 1 
   .FitToPagesWide = 1
  </code></pre>
  
  <p>Not sure why the <code>Zoom</code> line is needed, but that was the missing piece.</p>
  
  <p>Thanks to all who helped!</p>