Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Looping problem - Slowing down macro? Set print area dynamic

  1. #1
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:


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

    With ActiveSheet.PageSetup


    I would suggest changing these lines to:


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


    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?)


  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Jay!

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I gave you a bit of misinformation there, I told you to use:


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


    What you should use is:


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


    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.:


    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


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

    HTH

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-15 09:40 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark O'Brien,

    Thanks, will try that out! Am learning more and more each day!

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  9. #9
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:


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


    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.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-15 10:04 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •