Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extreme slow code if hiding rows (Office 2003)

    In my Excel VBA-code I need to hide rows based on a variable, this is a line I execute maybe 1000 times:

    Rows(i + 2 & ":" & i + 2).EntireRow.Hidden = True

    I found out that my code is extremely slow (takes several minutes); I traced the code and see no events firing. Application.screenupdating is false. If I comment out ONLY the few lines that hide the rows, the rest runs in a spilt second...

    Am I missing something here????

    (by the way; I'm not deleting a continuous range; the displayed line is inside a loop that determines for each 'i' if the row should be hidden)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extreme slow code if hiding rows (Office 2003)

    Does it help if you set calculation to manual at the beginning of your code, then set it to automatic again at the end? In Excel 2003, (un)hiding rows causes recalculation.

    Displaying page breaks is also known to slow down code that (un)hides rows, so you might insert the following line at the beginning:

    ActiveSheet.DisplayPageBreaks = False

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extreme slow code if hiding rows (Office 2003)

    The pagebreaks did it !!!

    Thanks very much!

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Extreme slow code if hiding rows (Office 2003)

    Hi ErikJan,

    Using:
    Rows(i + 2 ).EntireRow.Hidden = True
    might also run marginally faster, since it avoids the time taken to compute the lower boundary of the range - which in this case is the same as the upper boundary.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extreme slow code if hiding rows (Office 2003)

    Yep that makes sense, thanks

Posting Permissions

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