title: Excel VBA set print area to last row with data
body: <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>