[jakester]

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.

[JBeaucaire]

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

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

[jakester]

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

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.

[JBeaucaire]

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.