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