Looping problem - Slowing down macro? Set print area dynamic

[Mark O'Brien]

To answer part 1.

OK, usually using "Select" is a huge giveaway when you're trying to speed up macros. Selecting worksheets can take a second.

In the middle of your code you have:

<pre>
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

With ActiveSheet.PageSetup </pre>

I would suggest changing these lines to:

<pre>
With ActiveSheet.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address </pre>

I've not tested the code and I don't know if you can perform the operations that you want to do without the sheet being active, but this is worth a shot.

HTH

 

[Mark O'Brien]

How about:

<pre>
With ws.PageSetup
.PrintArea = ws.Range("A1", ws.Range("H65536").End(xlUp).Address).Address</pre>

 

[Cosmos75]

Mark O'Brien,

You are a genius! Works like a charm and MUCH faster!

 

[Mark O'Brien]

Sweet. I learned something and you learned something. Everyone's a winner.