I have an Excel table with a sheet "CR" where columns A to K are filled, with the first row being a header row.
Rows 1-1000 are formatted (borders) and column A contains a formula to autonumber the rows when data in column F is entered.
Sheet "CR" is protected to prevent users from entering data in column A (locked).
Using the Workbook_BeforePrint function, I'm trying to set the print area to columns A to K and to the last row of column A that contains a number.
My code (in object 'ThisWorkbook') is as follows:
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ws As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("CR") ' find the last row with data in column A lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ws.PageSetup.PrintArea = ws.Range("A1:K" & lastRow).Address End Sub
However, when I click File -> Print, the range of columns A to K up to row 1000 is displayed instead of only the rows that have a number in column A. What am I doing wrong?