Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.hpagebreaks
https://docs.microsoft.com/en-us/office/vba/api/excel.hpagebreaks

https://stackoverflow.com/a/63420741/5079799

Function for finding last row of last printed sheet:

Public Function LastPrintRow(ByRef strSheet As Worksheet) As Integer

Dim RowsPerPage As Long, PrintPages As Long
pgBreak = strSheet.HPageBreaks(1).Location.Address(False, False)
RowsPerPage = Right(pgBreak, Len(pgBreak) - 1) - 1
PrintPages = strSheet.HPageBreaks.Count + 1
LastPrintRow = RowsPerPage * PrintPages - PrintPages + 1
'Debug.Print LastPrintRow

End Function

Test:

Sub Test()

Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet

Dim lPrintRow As Integer
lPrintRow = LastPrintRow(ws)
Debug.Print lPrintRow

Dim UnUsedRng As Range
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
lColLet = Split(Cells(1, lCol).Address, "$")(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set UnUsedRng = ws.Range("A" & lRow & ":" & lColLet & lPrintRow)

UnUsedRng.Borders(xlBottom).LineStyle = xlContinuous
UnUsedRng.Borders(xlBottom).Weight = xlThin

End Sub
  • No labels