+ Reply to Thread
Results 1 to 7 of 7

Macro to Hide Zero Total Rows Runs VERY Slow

  1. #1
    Registered User
    Join Date
    12-03-2014
    Location
    California
    MS-Off Ver
    Office 2013 & 2010
    Posts
    26

    Question Macro to Hide Zero Total Rows Runs VERY Slow

    The attached pricing worksheet has with 14 sections and a total of 903 rows. I use a macro to hide sections with a zero total and that works fine. To reduce the number of printed pages, I also need to hide individual zero total rows within the sections that do not have a zero total.

    I created a macro to do that, but it runs very slowly. I suspect that it is due to my amateurish VBA coding.

    Any help would be appreciated. Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    Hi,

    You should turn off screen updating before run your code, and turn on after your code execution is finished.

    Please Login or Register  to view this content.
    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    12-03-2014
    Location
    California
    MS-Off Ver
    Office 2013 & 2010
    Posts
    26

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    So simple and worked great. The execution time dropped from 20+ seconds to about 1 second. Thank you so much!!!

  4. #4
    Registered User
    Join Date
    12-03-2014
    Location
    California
    MS-Off Ver
    Office 2013 & 2010
    Posts
    26

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    You know, there is something weird about this. When I open the file and run the macro, it runs quickly (~1 second). If I unhide the rows and run it again, it goes back to ~20 second run time. Maybe an Excel issue??? Thanks.

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    You are welcome.

    Not happenned here. How do you unhide the rows ? Manually or using another macro ?

    If you use a new, added/modified code, you can post the code here.

    Regards

  6. #6
    Registered User
    Join Date
    12-03-2014
    Location
    California
    MS-Off Ver
    Office 2013 & 2010
    Posts
    26

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    After further checking, the slow running does not occur simply by hiding and unhiding. I can do that over and over without a slowdown.

    The slow running only occurs after printing the worksheet with the rows hidden (either with a macro or manually [file > print...]), unhiding, and then re-running the Hide macro. I've attached the file, in case you want to see.

    I can live with it as is. Thank you so much.
    Attached Files Attached Files

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to Hide Zero Total Rows Runs VERY Slow

    You are welcome, many thanks for the rep. point.

    Macro performance is slow when page breaks are visible in Excel, as stated by Microsoft here :
    http://support.microsoft.com/kb/199505

    So, after you print / print preview, you should "hide" the page breaks :

    1. Manually :
    Click the Microsoft Office Button, and then click Excel Options
    Click the Advanced category, and then click to clear the Show page breaks check box under the Display options for this worksheet section

    2. Or by using macro like this :
    Please Login or Register  to view this content.
    Then run your macro, it should be much faster now.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Simple Script to hide rows - runs slow
    By davecox2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2014, 01:35 PM
  2. Macro to hide rows to slow
    By mort.marshall.20 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-15-2013, 12:47 PM
  3. [SOLVED] Hidding Rows Macro Runs Very Slow
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-13-2012, 10:55 AM
  4. [SOLVED] Macro runs slow
    By Sandy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-20-2005, 12:05 PM
  5. macro runs slow
    By nanomiter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2005, 01:06 PM

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