Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

Macro to set print area

Posted by Ghentry on January 14, 2002 7:27 AM
I have searched the message boards, but can't find exactly what I need. I am trying to find a macro that will set the print area. The first column is A, the last column will always be M. The first row to print will always be 1, the variable will be the last row with data. Any help would be appreciated.

Check out our Excel VBA Resources

Re: Macro to set print area

Posted by Richard Winfield on January 14, 2002 8:06 AM

You can use this macro to set the print area to the used range of the activesheet.

Sub SetPrintArea()
ActiveSheet.PageSetup.PrintArea = "=" & ActiveSheet.UsedRange.Address
End Sub

Hope this helps.

Rick


Re: Macro to set print area

Posted by Ghentry on January 14, 2002 9:20 AM
Thanks for your response, however, I guess I should have given you more information. Column A, Rows 8-1600 contains a formula on each row that calculates once information is entered in columns B through M of that same row. Since this code is looking for the used range, it is including all 1600 rows in the print area, even though only say the first 100 are filled in. Is there another way to do this?

Re: Macro to set print area

Posted by Juan Pablo G. on January 14, 2002 12:05 PM
How about this one then ?

Sub SetArea()
ActiveSheet.PageSetup.PrintArea = Range("A1",Range("M65536").End(xlUp)).Address
End Sub

Juan Pablo G.


Re: Macro to set print area

Posted by Ghentry on January 14, 2002 12:29 PM
Thanks. I'm heading out of the office, but I'll try it first thing tomorrow morning.


Re: Macro to set print area

Posted by Richard Winfield on January 14, 2002 12:48 PM

Here is a different approach

Sub setprintarea()

Dim myrange As String

myrange = Cells(Rows.Count, 13).End(xlUp).Address

ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange

End Sub

This looks for the last used cell in the M column and sets the print area accordingly.

Rick


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.