Search code examples
excelvbaprintingarea

Excel VBA set print area to last row with data

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?

Solution

  • Change:

    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    To:

    lastRow = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]