title: How can I "force" Excel to count page breaks correctly?
body: <p>I am supposed to export some large data ranges from Excel to Powerpoint, one page per slide, and of course I should treat the page breaks to avoid "orphan" rows or columns.</p>
  
  <p>I am trying to check how many pages I would have, vertically and horizontally, with a given zoom, by reading HPageBreaks.Count and VPageBreaks.Count, and then manually define the position of each break. The idea is to have approximately the same width and height on each page.</p>
  
  <p>When I debug my code step-by-step, it runs nicely, and the logic seems ok, but if I run it "freely", the page breaks are completely off. Adding some MsgBox instructions, I can see that when I read HPageBreaks.Count (or vertical) I get the wrong values. (I can check the correct ones if I do manually what the code should do).</p>
  
  <p>Searching on many many forums, I see some ugly workarounds like forcing a reset of PaperSize (ws.PageSetup.PaperSize = ws.PageSetup.PaperSize). After trying some of them, what seemed to work a bit better was to turn off PrintCommunication before a change to PageSetup, and then turn it back on. This worked well on most of my sheets, but on the really large ones (~750 rows x 80 columns, almost all cells with formulas), it simply doesn't.</p>
  
  <p>Here an extract of the code:</p>
  
  <pre><code>    'Reset page breaks
      .ResetAllPageBreaks
  
      'Set minimum acceptable zoom factor
      Application.PrintCommunication = False   'This is the ugly workaround
      .PageSetup.Zoom = 60
      Application.PrintCommunication = True
  
      MsgBox "Zoom = " &amp; .PageSetup.Zoom    'Only for debugging
  
      'Calculate the number of pages in width
      Application.PrintCommunication = False
      NPagesWide = .VPageBreaks.Count + 1
      Application.PrintCommunication = True
  
      MsgBox "NPagesWide = " &amp; NPagesWide
  
      'Find the higher zoom factor that can fit that number of pages
      Application.PrintCommunication = False
      .PageSetup.Zoom = 100
      Application.PrintCommunication = True
      Do While .VPageBreaks.Count &gt; NPagesWide - 1
          Application.PrintCommunication = False
          .PageSetup.Zoom = .PageSetup.Zoom - 5
          Application.PrintCommunication = True
      Loop
  
      MsgBox "Zoom = " &amp; .PageSetup.Zoom
  
      'Set average width per page and initialize column pointer
      If HasTitleColumns Then     'Defined earlier
          PageWidth = (PrintArea.Width + TitleColumns.Width * (NPagesWide - 1)) / NPagesWide
          j = TitleColumns.Columns(TitleColumns.Columns.Count).Column + 1
      Else
          PageWidth = PrintArea.Width / NPagesWide
          j = 1
      End If
  
      'Cycle vertical page breaks
      For i = 1 To NPagesWide - 1
          'Set width of TitleColumns
          If HasTitleColumns Then
              CumulWidth = TitleColumns.Width
          Else
              CumulWidth = 0
          End If
          'Cumulate columns width until the available page width
          Do While CumulWidth + .Columns(j).Width &lt;= PageWidth
              CumulWidth = CumulWidth + .Columns(j).Width
              j = j + 1
          Loop
          'Add the break
          .VPageBreaks.Add .Columns(j + 1)
      Next i
  </code></pre>
  
  <p>Any ideas why this happens, and how can I solve it?</p>
  
  <p>Thanks,</p>