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