Excel VBA slow on Print Range settings

[xlStar]

I've created macro to create several reports. Each report have different layout, portrait or lansacpe etc. When the macro applied print range settings it seems quite slow, on my PC the whole job takes about half an hour, but on other department, with slower PC can take up to 4 hours. Is there a speedy way of applying print range? Here one of my script, can you make it faster...


Sub PrintLandscape(yend, xend)

ActiveSheet.PageSetup.PrintArea = "$A$1:$" & yend & "$" & xend

With ActiveSheet.PageSetup
.CenterFooter = "&A"
.LeftMargin = Application.InchesToPoints(0.3)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.33)
.BottomMargin = Application.InchesToPoints(0.7)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.33)
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub

 

[GlennUK]

You may be better off posting this to the VBA forum.

Do pagesetup inside VBA has always been slow, and the only way I use to speed things up is to translate the required settings into Excel 4 settings for PAGE.SETUP Excel 4 macro command, and issue the command through ExecuteExcel4Macro VBA command.

Do you really want to go down this route? It's messy and hard to debug if things go wrong.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlStar]

GlennUK

Could you able to show me one example of ExecuteExcel4Macro VBA command for the print range?

 

[GlennUK]

The print range is set using a command like this:

Application.ExecuteExcel4Macro ("SET.PRINT.AREA(""A2:G17"")")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.