EddyH:
I've always found Tony's tips to be on the mark and very helpful so I've nothing to be upset about over his star. But, "disentangle", now that hurts! :-<
To walk you through it:
If you make a call to GetFinalPageCounts (See updated code below),
first the function turns off screen updating to keep the user from seeing the screen jumps, then it stores the users current viewpoint so we can put them back where they were when the function was called.
Next, the function jumps to the last used printable cell and scrolls it onto the active window pane so the HPageBreaks and VPageBreaks objects will be fully populated.
Now the function can capture the items of interest, the final row and column to be printed and the first row and column of the final page.
Finally, the function returns the screen to where the user was when we started and it computes and returns the number of rows and columns that will be printed on the final page.
After you make the call to this function the HPageBreaks and VPageBreaks objects will have been populated so you can get the row numbers at each of the page breaks (Your original question) by looping through the collection with:
For Each pb In Activesheet.HPageBreaks
Debug.Print pb.Location.Row
Next
Skip's FAQ points out that my original code only works when your dealing with a printout that is one page wide by multiple pages long. The code below incorporates Skip’s function and is updated to handle columns as well as rows.
SKIP: **NOTE** There is an error on your FAQ. You need to add one to the HPageBreak.Count as well as the VPageBreak.Count since a page break always occurs between two pages.
If you place a button on a toolbar and assign it to the Check_It subroutine then you can play with the Row Height, Column Width, Rows at Top of each page, and The Columns at the left of each page, and check the results of your changes easily.
Just remember that each row's height can be independantly manipulated so trying to determine how to adjust row heights to preclude having a small number of rows on the last page is not a straight forward calculation.
Hope this has helped to "disentangle" my code.
Good Luck with your project,
Ron
=============== Updated Code ====================
Public Enum ViewPointAction
SaveViewpoint = 1
RestoreViewpoint = 2
End Enum
Public Type CellsPrinted
RowCount As Integer
ColumnCount As Integer
End Type
Function TotalPrintPagesOnWorksheet() As Integer
With ActiveSheet
TotalPrintPagesOnWorksheet = (.HPageBreaks.Count + 1) * (.VPageBreaks.Count + 1)
End With
End Function
Sub SelectRealLastCell()
Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Range("A1"
.Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"
, xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"
, xlFormulas, , xlByColumns, xlPrevious).Column
Cells(lRealLastRow, lRealLastColumn).Select
End Sub
Sub ManageUserViewpoint(Action As ViewPointAction)
Static SaveCurSelection As String
Static SaveActiveCell As String
Static SaveCurViewRow As Long
Static SaveCurViewCol As Integer
If Action = RestoreViewpoint Then
ActiveSheet.Range(SaveCurSelection).Select
ActiveSheet.Range(SaveActiveCell).Activate
ActiveWindow.ActivePane.ScrollRow = SaveCurViewRow
ActiveWindow.ActivePane.ScrollColumn = SaveCurViewCol
Else
SaveCurSelection = Selection.Address
SaveActiveCell = ActiveCell.Address
SaveCurViewRow = ActiveWindow.ActivePane.ScrollRow
SaveCurViewCol = ActiveWindow.ActivePane.ScrollColumn
End If
End Sub
Function GetFinalPageCounts() As CellsPrinted
Dim LastUsedRow As Long
Dim LastUsedColumn As Long
Dim ScreenUpdatingFlag As Boolean
Dim FirstRowOfLastPage As Long
Dim FirstColumnOfLastPage As Long
ScreenUpdatingFlag = Application.ScreenUpdating
Application.ScreenUpdating = False
ManageUserViewpoint (SaveViewpoint)
SelectRealLastCell
'You must not only select the last cell, you need to roll it onto the
' screen for Excel to populate the HPageBreaks and VPageBreaks collections !?!?
ActiveWindow.ActivePane.ScrollRow = Selection.Row
ActiveWindow.ActivePane.ScrollColumn = Selection.Column
LastUsedRow = ActiveCell.Row
LastUsedColumn = ActiveCell.Column
FirstRowOfLastPage = ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location.Row
FirstColumnOfLastPage = ActiveSheet.VPageBreaks(ActiveSheet.VPageBreaks.Count).Location.Column
ManageUserViewpoint (RestoreViewpoint)
Application.ScreenUpdating = ScreenUpdatingFlag
GetFinalPageCounts.RowCount = LastUsedRow - FirstRowOfLastPage + 1
GetFinalPageCounts.ColumnCount = LastUsedColumn - FirstColumnOfLastPage + 1
End Function
Sub CheckIt()
Dim FinalPageCounters As CellsPrinted
Dim Index As Integer
Dim Message As String
FinalPageCounters = GetFinalPageCounts
MsgBox "There are " & vbCr & FinalPageCounters.RowCount & _
" rows and " & vbCr & FinalPageCounters.ColumnCount & " columns " & _
vbCr & "on the last page, Page " & TotalPrintPagesOnWorksheet
Message = "Page starting rows are as follows: " & vbCr & vbCr
Index = 1
If ActiveSheet.PageSetup.Order = xlOverThenDown Then
For Each pb In ActiveSheet.HPageBreaks
Index = Index + ActiveSheet.VPageBreaks.Count + 1
Message = Message & "Page " & Index & " Begins with row: " & pb.Location.Row & vbCr
Next
Else
For Each pb In ActiveSheet.HPageBreaks
Index = Index + 1
Message = Message & "Page " & Index & " Begins with row: " & pb.Location.Row & vbCr
Next
End If
MsgBox Message
End Sub
