+ Reply to Thread
Results 1 to 5 of 5

VBA code running horribly slow

  1. #1
    Forum Contributor
    Join Date
    08-03-2009
    Location
    Cary, NC
    MS-Off Ver
    Excel 2016
    Posts
    109

    VBA code running horribly slow

    SlowVBA.xlsm

    I'm running Excel 2016. I'm running the following code:

    Please Login or Register  to view this content.
    I'm using the following Code Optimizations:

    Please Login or Register  to view this content.
    If I run this code with the Code Optimization commented out it takes 1 minute 40 seconds to run. We're talking about 69 rows here... If I run it WITH the Code Optimization it still takes a full minute to run! What am I doing wrong?
    Last edited by bishoposiris; 02-09-2016 at 09:41 AM. Reason: Solved

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA code running horribly slow

    It's not usual to try to decrement a loop counter within a loop. I suspect it's not having the effect you think it should, but it might be causing some confusion.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA code running horribly slow

    And, to delete cells in a loop, it's normal to start at the bottom and work up. That is, ... Step -1

  4. #4
    Forum Contributor
    Join Date
    08-03-2009
    Location
    Cary, NC
    MS-Off Ver
    Excel 2016
    Posts
    109

    Re: VBA code running horribly slow

    Changing my code to implement the Step - 1 syntax did the trick:

    Please Login or Register  to view this content.
    This runs instantaneously even without the Code Optimization. Thank you!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA code running horribly slow

    You're welcome. Thanks for the rep

    Suggest you remove the redundant code in order to make it easier to read And anything to do with row counters should be Long rather than Integer (mostly just in case, but I believe processing Long variables is also more efficient)

    Please Login or Register  to view this content.

    Regards, TMS

+ 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. Slow running Code
    By Ausadian in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2015, 04:41 PM
  2. Slow running code
    By phil2006 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2013, 08:02 AM
  3. vba code running too slow
    By hitsujicute in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2013, 06:00 PM
  4. VBA Code running very slow. Need help
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-21-2013, 02:13 PM
  5. Autosave/close gone horribly wrong - where to place code?
    By Greed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2012, 10:07 AM
  6. Slow running code
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2008, 12:49 PM
  7. Code running slow
    By lou031205 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-08-2007, 12:20 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