Ask Experts Questions for FREE Help !
Ask

Question about Setting Print Range

Asked Aug 18, 2010, 11:58 AM — 3 Answers
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.

Search this Thread
Share |
3 Answers
JBeaucaire's Avatar
JBeaucaire Posts: 5,377, Reputation: 5041
Software Expert
  
#2

Aug 18, 2010, 08:20 PM



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
Helpful  (1)
jakester's Avatar
jakester Posts: 560, Reputation: 831
Senior Member
  
#3

Aug 19, 2010, 12:51 PM
Quote:
Originally Posted by JBeaucaire View Post
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.
Helpful
JBeaucaire's Avatar
JBeaucaire Posts: 5,377, Reputation: 5041
Software Expert
  
#4

Aug 19, 2010, 04:36 PM


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.
Helpful
Not your question? Ask your question View similar questions
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Add your answer here.

Remove Text Formatting
 

Undo
Redo
  
Decrease Size
Increase Size
Bold
Italic
Underline
Align Left
Align Center
Align Right
Ordered List
Unordered List
Decrease Indent
Increase Indent
Insert Email Link
Wrap [QUOTE] tags around selected text
Wrap [CODE] tags around selected text
Wrap [HTML] tags around selected text
Wrap [PHP] tags around selected text
Wrap [YOUTUBE] tags around selected text
Notification Type:



Check out some similar questions!

I select print from yahoo email, the lexmark printer Z517 will only print in color [ 1 Answers ]

Just connected a lexmark printer Z517, put a new color and black ink cart in and installed the driver, it won't print in black, jst color. Part of the black text will not print. Could the black ink cart be bad as I bought it from walmart.

What are your long-range and short-range career goals and objectives, and how are you [ 1 Answers ]

Hello, What are your long-range and short-range career goals and objectives, and how are you preparing to achieve them?

Changing from a gas range to an electric smooth top range [ 1 Answers ]

What are the requirement for switching from cooking with a gas stove to cooking with and electric stove

View more Spreadsheets questions Search