Ok, here is my problem.

I'm looking for something that will evaluate how many rows there are by looking to the last row in column A that has data, and then setting the print range to Column N of that same row. Here's my one condition: I have a formula that is calculated in Column N that runs all the way down to Row 500 (just in case I need that many records in a given month). When I print, I only want to see the Data Range where Column N is actually calculating something.

So, let's say my worksheet currently has 50 records that I want to print. I would only want to print A4:N54. I don't want to print A4:N504, though. If I set the print area to the Active Sheet area it tries to print all of the 504 records.

How can I search the last row in Column A and then set that last row as a Variable for Column N, such that the Print Area will to be set to A:$N$ and know what the last row variable is?
Thanks, guys.

Try putting this macro into the ThisWorkbook module. It will set the print area as soon as you try to print the sheet.

Code:

---

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
   
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & LR
    ActiveSheet.PrintOut Copies:=1

End Sub

---

Quote:

---

Originally Posted by JBeaucaire

Try putting this macro into the ThisWorkbook module. It will set the print area as soon as you try to print the sheet.

Code:

---

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
   
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & LR
    ActiveSheet.PrintOut Copies:=1

End Sub

---

---

JBeaucaire - do you take donations through Paypal?

Thanks for your help.

There's a link to my website in my signature, I have published a lot of free code there. Mine as much from there as you can.

There are links there for making donations if you really want to. ;)

Glad it worked for you.