title: How to identify page breaks in excel with VBA, varying conditions
body: <p>This is a problem I've been wrestling with for some time.  Forgive me for not citing the exact threads I've researched, but I've not been keeping a log.  The code sample I have below has been constructed from reviewing many threads on this topic.  However, the exact solution I need still evades me.</p>
  
  <p>Simply put: In Excel VBA, I need to be able to identify the last row and first/last column of each page in a long table which could be from 1 to 5 pages long (horizontally), after having been dynamically created and populated. I would also appreciate a quick way to identify where the populated data stops, but can get that from the generating routine if there's no easy solution. The following code will identify the correct final row and column of a single page table... once. I can make the modification needed to check subsequent pages for a multi-page table.  The real problem I'm having is that once a cell has been populated and cleared, excel includes that cell in the range of used cells.  Subsequent executions of this same code fail, because the last cell is no longer correctly identified.  Is there anyway to reverse this, or a different approach I could take?</p>
  
  <p>I truly do not like populating cells and deleting them to find the page breaks, but I've not found a solution that avoids doing so.  Thanks for any guidance you might provide.   ,Mike Shanahan</p>
  
  <pre><code>Sub Findpagebreaks()
  
  Dim x As HPageBreaks, pb_x As HPageBreak
  Dim y As VPageBreaks, pb_y As VPageBreak, PageMatrix() As Integer
  Dim LastPopulated(1) As Integer, test As Integer, target As Integer
  
  ReDim PageMatrix(1 To 1, 0 To 2) As Integer
  With ActiveSheet
      Debug.Print "============================================"
  
      Debug.Print "Horizontal Page Breaks"
      Set x = .HPageBreaks
      Debug.Print "Initial Hbreaks: ", x.Count
      LastPopulated(0) = .Cells.SpecialCells(xlCellTypeLastCell).Row
      Debug.Print "Last row of data: ", LastPopulated(0)
      target = x.Count + 1
      test = LastPopulated(0)
      Do While x.Count &lt; target
          test = test + 10
          .Cells(test, 1).Value = "."
          Debug.Print "cell: " &amp; .Cells(test, 1).Address &amp; " populated"
          If test &gt; 100 Then Exit Do
      Loop
      For Each pb_x In x
          If pb_x.Extent = xlPageBreakFull Then
              Debug.Print "Row: " &amp; pb_x.Location.Row, "Full Page Break"
              PageMatrix(1, 2) = pb_x.Location.Row - 1
          Else
              Debug.Print "Row: " &amp; pb_x.Location.Row, "Partial Page Break"
          End If
      Next pb_x
      .Range(.Cells(LastPopulated(0) + 1, 1), .Cells(test, 1)).ClearContents
      Debug.Print "cells: " &amp; .Range(.Cells(LastPopulated(0), 1), .Cells(test, 1)).Address &amp; " cleared."
      Debug.Print "Horizontal Exploration complete."
  
  
      Debug.Print "Vertical Page Breaks"
      Set y = .VPageBreaks
      Debug.Print "Initial vbreaks: ", y.Count
      LastPopulated(1) = .Cells.SpecialCells(xlCellTypeLastCell).Column
      Debug.Print "Last column of data: ", LastPopulated(1)
      target = y.Count + 1
      test = LastPopulated(1)
      Do While y.Count &lt; target
          test = test + 10
          .Cells(1, test).Value = "."
          Debug.Print "cell: " &amp; .Cells(1, test).Address &amp; " populated"
          If test &gt; 100 Then Exit Do
      Loop
      PageMatrix(1, 0) = 1
      For Each pb_y In y
          If pb_y.Extent = xlPageBreakFull Then
              Debug.Print "column: " &amp; pb_y.Location.Column, "Full Page Break"
              PageMatrix(1, 1) = pb_y.Location.Column - 1
          Else
              Debug.Print "Row: " &amp; pb_y.Location.Column, "Partial Page Break"
          End If
      Next pb_y
      .Range(.Cells(1, LastPopulated(1) + 1), .Cells(1, test)).ClearContents
          Debug.Print "cells: " &amp; .Range(.Cells(1, LastPopulated(1)), .Cells(1, test)).Address &amp; " cleared."
      Debug.Print "Vertical Exploration complete."
  
  
      Debug.Print "Page", "First Col", "Last Col", "Last Row"
      Debug.Print 1, PageMatrix(1, 0), PageMatrix(1, 1), PageMatrix(1, 2)
      Debug.Print "~~~~~~~~~~~~~~~~~~~~~~"
      Debug.Print "Sub complete."
      Debug.Print
  
  End With
  End Sub
  </code></pre>