+ Reply to Thread
Results 1 to 10 of 10

Speed up code? This simple VBA code makes me a report but its taking 15 minutes to finish.

  1. 05-17-2018, 08:37 PM #1
    1.jclozano
    1.jclozano is offline
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Speed up code? This simple VBA code makes me a report but its taking 15 minutes to finish.

    This macro is for a bus workshop, it reviews the excel DB and makes me a report of all the expenses per bus which ends up giving 2k rows.

    Can you please tell me how to speed up this macro? 15 minutes is a lot of time.

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 05-17-2018 at 09:18 PM. Reason: Please use code tags when posting code!
    Reply With QuoteRegister To Reply
  2. 05-17-2018, 09:31 PM #2
    BoredWorker
    BoredWorker is offline
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Hi,
    you may try this.

    using of With, direct equal and avoid using select would save time.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 05-17-2018, 09:35 PM #3
    1.jclozano
    1.jclozano is offline
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Let me try it out and I'll get back to you on the time difference! <3
    Reply With QuoteRegister To Reply
  4. 05-17-2018, 10:00 PM #4
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Fastest way to accomplish this would be to create entire array in memory and dump it onto Report sheet in 1 go.

    Post example file with some data in it so we have something to work with. Desensitize data if needed.

    Just a wild guess.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 05-17-2018 at 11:09 PM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  5. 05-18-2018, 04:28 PM #5
    1.jclozano
    1.jclozano is offline
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    trying the code!
    Last edited by 1.jclozano; 05-18-2018 at 05:40 PM.
    Reply With QuoteRegister To Reply
  6. 05-18-2018, 08:05 PM #6
    1.jclozano
    1.jclozano is offline
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Quote Originally Posted by bakerman2 View Post
    A
    Quote Originally Posted by boredworker View Post
    A
    check this code out, this was the solution:

    I'M PERPLEXED, THANK YOU SO MUCH!!! YOU DESERVE A BETTER future



    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-18-2018, 08:28 PM #7
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Glad you got it working.

    Just be carefull with using Redim Preserve. It will give some overhead since the array is recalculated on every run.

    What about execution time ?
    Reply With QuoteRegister To Reply
  8. 05-18-2018, 11:40 PM #8
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,516

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Duplicate. Deleted.
    Last edited by jolivanes; 05-18-2018 at 11:42 PM.
    Reply With QuoteRegister To Reply
  9. 05-18-2018, 11:41 PM #9
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,516

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    https://www.ozgrid.com/forum/forum/h...ing-15-minutes

    Please read Forum Rules. You would be interested in #8.
    Reply With QuoteRegister To Reply
  10. 05-21-2018, 04:34 PM #10
    1.jclozano
    1.jclozano is offline
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Re: Speed up code? This simple VBA code makes me a report but its taking 15 minutes to fin

    Quote Originally Posted by bakerman2 View Post
    Glad you got it working.

    Just be carefull with using Redim Preserve. It will give some overhead since the array is recalculated on every run.

    What about execution time ?
    It went from 15 minutes to 5 seconds! I need to understand the code better to use it for the input/output section


    Jolivanes I apologize for my mistake, I'll go read the forum rules again
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How 2 merge into 1 code 4 report, Actually i'm using same code 4 times to produce 4 repor
    By Franky alta in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-17-2016, 07:34 PM
  2. Agging report, openingbalance report,customer report vab code was not working
    By rana19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2015, 03:38 AM
  3. Agging report, openingbalance report,customer report vab code was not working
    By rana19 in forum Excel General
    Replies: 4
    Last Post: 08-18-2015, 10:02 AM
  4. [SOLVED] VBA Delete Row Code - Need more efficient code to speed it up
    By matcapir in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2015, 04:32 PM
  5. VBA code to analyse data unless function code makes more sense than VBA,
    By vorabj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 12:38 PM
  6. [SOLVED] Vba code makes workboos slow. Any alternate code ??
    By satputenandkumar0 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-23-2014, 04:30 AM
  7. [SOLVED] Slow VBA Code for simple Loop and add pasted values Any Way to Speed Up
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2013, 11:16 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1