+ Reply to Thread
Results 1 to 9 of 9

Rows hide - speed optimisation

  1. #1
    Registered User
    Join Date
    10-30-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Lightbulb Rows hide - speed optimisation

    I am using the following code to hide rows, that correspond with a certain criteria.

    Please Login or Register  to view this content.
    However, it takes Excel hours to finish the calculation and hide the desired rows.
    Please advise me how to optimise the script.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Rows hide - speed optimisation

    Please Login or Register  to view this content.
    Why would you want to do that in the calculate event?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-30-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Rows hide - speed optimisation

    Well, it shouldn't necesarily be a calculative event. However, if I don't miss something, no matter what the event is, it will take the same time to complete.

    The trigger is actually a range of dates that rerer to certain rows.

  4. #4
    Registered User
    Join Date
    10-30-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Rows hide - speed optimisation

    Thanks shg, it is much better now . Still, it takes about a minute to complete.

    I actually need it once a month, but have no idea how to "shedule" a recalculation with such occurance...

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Rows hide - speed optimisation

    It should take a lot less time now. When you hide a row in Excel 2003, it triggers a calculation, so it would execute the code repeatedly.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Rows hide - speed optimisation

    Try changing it to this. It will execute when the sheet is activated.
    Please Login or Register  to view this content.

  7. #7
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,205

    Re: Rows hide - speed optimisation

    This will restrict the action to only the relavant rows. And allow formulas that return logicals to control the row visiblity
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Rows hide - speed optimisation

    Mike's approach is, of course, valid. The two approaches, in general, will give different results, so look and see which, if either, does what you want.

  9. #9
    Registered User
    Join Date
    10-30-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Rows hide - speed optimisation

    Quote Originally Posted by shg View Post
    Try changing it to this. It will execute when the sheet is activated.
    Please Login or Register  to view this content.
    I tried a number of different scenarios, the most efficient of which proved to be the one that triggers a calculation on sheet deactivation. This elliminated the "calculation lag" of the model.

    The code is as follows:

    Please Login or Register  to view this content.

    Shg and mikerickson, thank you both for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1