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.

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

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?

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.

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.

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