+ Reply to Thread
Results 1 to 5 of 5

Sort in Macro Runs SLOW Compared to Manual

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Sort in Macro Runs SLOW Compared to Manual

    I have a macro that works on a large table of data (like 350K rows). In order to delete all rows that don't meet certain criteria, I write a "flag" formula (that results in 1 or 0) into an empty column, then sort on that flag column, and delete the entire block of rows where the flag = 0.

    My problem is that the sort operation takes MUCH longer when the macro does it, than when I do the same sort manually, on the same data. It takes maybe 10 seconds or so when I sort it manually, but when the macro runs the sort (stepping through it, or letting it run), it takes minutes, and much of that time I see "(Not Responding)" in the title bar.

    Any ideas on what's happening or how to fix it? I'm running Excel 2007 on a Windows 8 computer.

  2. #2
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Sort in Macro Runs SLOW Compared to Manual

    Need to see your macro, really. Can you upload a (desensitized) copy?

    I assume you've done the usual stuff at the beginning, like
    Please Login or Register  to view this content.
    Tim
    Never stop learning!
    <--- please consider *-ing !

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Sort in Macro Runs SLOW Compared to Manual

    Screen updating is disabled in the macro, although I doubt that affects the sort operation, and in any event, it runs FASTER manually (when screen updating is NOT disabled).
    Calculation is still enabled, but that should not affect it, since no values are changed during the sort (and calc is also enabled when running it manually).
    I do not have any event macros, so EnableEvents should not affect it either.
    Here is the line of code that takes so long:
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Sort in Macro Runs SLOW Compared to Manual

    Quote Originally Posted by Merf View Post
    delete the entire block of rows where the flag = 0.
    Reverse : 0 = keep, 1 = delete then clear the block of 1 as clearing can be faster than deleting …

    Conditional formatting may lead a worksheet to be slow.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Sort in Macro Runs SLOW Compared to Manual

    The trick is to know what the criteria is. For people willing to help anyway.

+ 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. Same Code runs different in VBA-Macro VS manual clicking
    By geshak83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2016, 02:51 PM
  2. [SOLVED] macro runs slow on save
    By Fred33 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2015, 04:25 PM
  3. [SOLVED] Macro with calendar runs slow
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2013, 01:02 PM
  4. Excel2003 Macro runs slow???
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2006, 01:55 AM
  5. After printing, macro runs slow???
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2006, 10:38 PM
  6. [SOLVED] Macro runs slow
    By Sandy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-20-2005, 12:05 PM
  7. macro runs slow
    By nanomiter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2005, 01:06 PM

Tags for this Thread

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