macro to select print area

[JRS]

Does anyone know a good way I can select print area using a macro.
At the moment I have recorded a macro of me clicking on page setup and then on "landscape" and then on "fit to 1 page wide by 1 page height".

When I run the macro, it does the job but takes about 10 seconds to run, and the screen is flickering the whole time, not a very professional look.
There must be a simpler way to do it, that only takes the code a fraction of a second to implement?

Cheers
JRS

 

[JamesW]

Hi,

Try adding Application.ScreenUpdating = False to the start and Application.ScreenUpdating=True to the end.

 

[JRS]

That certainly stopped the screen flickering, I am really after a bit of code that speeds everything up though. There must be some that that says:

set print area to rangeA1:H20)
set print option to be landscape


If there is anything like that it would surely take less than a second to execute.
Any ideas?

Cheers for the last bit of code though, its certainly an improvement

 

[JamesW]

Can you link your code using [ CODE ] [ /CODE ] tags?

 

[JRS]

[ CODE ]

ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

[ /CODE ]