title: Dynamically Setting Page Breaks So Blank Pages Don't Print
body: <p>I have a spreadsheet that is being used for quoting. I have 8 different pages that are all vertically aligned. I have page breaks set but when I decide I don't want a page I have it automatically hiding. However, since I have page breaks set, when I export to a PDF I get blank pages. I'd like to dynamically get rid of these blank pages when exporting to a PDF. I'm a mechanical engineer and haven't written much code so don't laugh too hard :).</p>
  <p>I can write a concatenated function in excel to say which lines to insert page breaks above. The reason I like the cell being generating the page break numbers is if I insert a line later it will automatically update.
  Here is what I belive the steps would be in VBA:</p>
  <ol>
  <li>Remove all page breaks</li>
  <li>Set column page break to the left of column AU</li>
  <li>Set row page breaks based on the values generated by cell AX2</li>
  </ol>
  <p>Here is what I have so far on the code which isn't quite working out.</p>
  <pre><code>ActiveSheet.ResetAllPageBreaks
  ActiveSheet.Columns(47).PageBreak = xlPageBreakManual
  ActiveSheet.Rows(&quot;AX2&quot;).PageBreak = xlPageBreakManual
  </code></pre>
  <p>For reference my formula for cell AX2==CONCATENATE(IF(AU76&lt;&gt;&quot;&quot;,ROW(AU77),&quot;&quot;),IF(AU199&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU200),&quot;&quot;),IF(AU342&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU343),&quot;&quot;),IF(AU403&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU404),&quot;&quot;),IF(AU454&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU455),&quot;&quot;),IF(AU507&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU508),&quot;&quot;),IF(AU555&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU556),&quot;&quot;),IF(AU597&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU598),&quot;&quot;),IF(AU652&lt;&gt;&quot;&quot;,&quot; , &quot;&amp;ROW(AU653),&quot;&quot;))</p>
  <p>Which at the moment is yielding 77 , 200 , 343</p>
  <p>Also, I'm using this within the macro to export to a PDF. I'd insert this page break code before the export to pdf. Here is that code for reference as well:</p>
  <pre><code>Sub ExportToPDF()
  '
  ' ExportToPDF Macro
  '
  
  '
  
      ActiveSheet.Unprotect Password:=&quot;Password&quot;
      ActiveSheet.Range(&quot;$A$5:$AU$652&quot;).AutoFilter Field:=47, Criteria1:=&quot;&lt;&gt;&quot;
      ActiveSheet.Range(&quot;$A$5:$AV$652&quot;).AutoFilter Field:=48, Criteria1:=&quot;&lt;&gt;&quot;
      Dim Path As String
      Dim filename As String
      Path = &quot;Destination&quot;
      filename = Range(&quot;AY15&quot;)
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
          Path &amp; filename &amp; &quot;.pdf&quot;, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      ActiveSheet.Range(&quot;$A$5:$AV$652&quot;).AutoFilter Field:=48
      ActiveSheet.Protect Password:=&quot;Password&quot;, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True
  End Sub
  </code></pre>