Excel VBA set print area to last row with data
<p>I have an Excel table with a sheet "CR" where columns A to K are filled, with the first row being a header row.</p>

<p>Rows 1-1000 are formatted (borders) and column A contains a formula to autonumber the rows when data in column F is entered.</p>

<p>Sheet "CR" is protected to prevent users from entering data in column A (locked).</p>

<p>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.</p>

<p>My code (in object 'ThisWorkbook') is as follows:</p>

<pre><code>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" &amp; lastRow).Address
End Sub
</code></pre>

<p>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?</p>