Looping problem - Slowing down macro? Set print area dynamic

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Created code to fit certain worksheet to fit to 1 page wide and 1 page tall.

Here's my code. Had it exclude certain worksheets. But it takes a couple of seconds for each worksheet. Is because it's looping though the excluding certain worksheets part? How can I get it to exclude them once and for all and then set print areas for all other sheets??

Also, I want to just set the print area to print on ONE page only.
Can I just use only

.FitToPagesWide = 1
.FitToPagesTall = 1

and exclude the rest?
From .LeftHeader = ""
to .Zoom = False

THANKS!

CODE:
For Each ws In Worksheets
If ws.Name<> "Summary" Then
If ws.Name<> "Test" Then
If ws.Name<> "Test2" Then
If ws.Name<> "Test3" Then
If ws.Name<> "Test4" Then
If ws.Name<> "Test5" Then
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address

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 = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End If
End If
End If
End If
End If
End If

Next ws
End Sub
This message was edited by Cosmos75 on 2002-04-15 10:44
 
Upvote 0
So, it's the ws.select that is making it slow and not this part?

For Each ws In Worksheets
If ws.Name <> "Summary" Then
If ws.Name <> "Test" Then
If ws.Name <> "Test2" Then
If ws.Name <> "Test3" Then
If ws.Name <> "Test4" Then
If ws.Name <> "Test5" Then

Is there a way to have it exclude those sheets only once instead of checking each shet to see if it's those 6 sheets to be excluded? (Or is it already doing that?)
 
Upvote 0
I believe that the slow part is the formatting. VBA is very slow in this, compared to Excel4 macros, or so I've read.

Try to quicken the formatting part by getting rid of the default values that you are not changing anyway.

Mark's suggestions also will help a lot. I'll address your other question after (if) I figure it out.

Bye,
Jay
 
Upvote 0
Sorry, I gave you a bit of misinformation there, I told you to use:<pre>
With ActiveSheet.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address</pre>

What you should use is:<pre>
With ws.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address</pre>

Those "If" statements shouldn't really be slowing you down too much. I can't really think of an easy way to only run this check once. However, one thing I would suggest is having a look at "Select Case" instead of the "If...Then" statements. e.g.:<pre>
Dim ws As Worksheet


For Each ws In Worksheets
Select Case ws.Name

Case "Test", "Test2", "Test3" '....etc, complete this yourself
'Do nothing
Case Else
'Your Code here
End Select

Next</pre>

The reason being is that "Select Case" is usually faster than several "If..Then" statements.

HTH

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-15 09:40
 
Upvote 0
Mark O'Brien,

Changed your

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

to this (removed repeated .PageSetup)

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

I tried this

With ws.PageSetup
.PrintArea = Range("A1", Range("H65536").End(xlUp).Address)
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Doesn't work. Error with

.PrintArea = Range("A1", Range("H65536").End(xlUp).Address)

Haven't tried the case part yet...
 
Upvote 0
Oh yeah and you're going to have to qualify the ranges now. i.e. reference them fully. So in this case you want to put:<pre>
.PrintArea = ws.Range("A1", ws.Range("H65536").End(xlUp).Address)</pre>

We'll get this sorted. I promise.

I just had a quick look a the code and I think this is the only line that should need this.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-15 10:04
 
Upvote 0
Mark O'Brien,

I am so sorry for this trouble but it isn't working.

I get a error message

Run-time error '13':
Type mismatch

With ws.PageSetup
.PrintArea = ws.Range("A1", ws.Range("H65536").End(xlUp).Address)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top