+ Reply to Thread
Results 1 to 7 of 7

Macro efficiency - Execute process faster

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    44

    Macro efficiency - Execute process faster

    Hello all... again!!

    I have a macro, that works totally fine, it is just SLOW AS HECK!!

    here is the situation, and maybe what I already have is the best it can do... I just don't want to believe it though.. lol

    ok.. I have a spreadsheet that has OVER 150000 rows, some of these rows have a duplicated value in a column (Column 'C').

    I have this macro that steps backward from the bottom removing the duplicated rows based on that value, but before it does, it copies
    the value from Column 'I' and pastes it into column 'G', then it will delete the original duplicate row.

    it all works, but since I am not the VBA god as MANY on here, I was wondering for future knowledge if this could be written to be more efficient.

    Thanks, and the code is below...

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,985

    Re: Macro efficiency - Execute process faster

    A really little one that might save a little time is to not copy/paste the I value but simply place it in the cell ie

    Please Login or Register  to view this content.
    I base this on nothing other than the belief that Copy/Paste must be more intensive than = plus it loses 2 lines of code.

    Alternatively, you could CUT the value from I and therefore remove the need for the .ClearContents line
    If someone has helped you then please add to their Reputation

  3. #3
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Macro efficiency - Execute process faster

    try
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-01-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Macro efficiency - Execute process faster

    kalak,

    That was a ton faster.. I didn't even have to get up and walk away from the desk when it was executed.

    now, since this is a huge learning opportunity for me, could you further explain or breakdown the code so I can understand how it is working?

    if you have the time...

    I will no doubt have many more uses for it as I regularly have worksheets with close to the maximum rows of data to consolidate and would like to understand further.

  5. #5
    Registered User
    Join Date
    02-01-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Macro efficiency - Execute process faster

    Quote Originally Posted by pjwhitfield View Post
    A really little one that might save a little time is to not copy/paste the I value but simply place it in the cell ie

    Please Login or Register  to view this content.
    I base this on nothing other than the belief that Copy/Paste must be more intensive than = plus it loses 2 lines of code.

    Alternatively, you could CUT the value from I and therefore remove the need for the .ClearContents line

    pjwhitfield,

    I did incorporate your option as well on my test sheet.

    It did reduce the execute time by a little and I do see your point on the reasoning for the suggestion.

    Thank you..

    This VBA stuff... always seems like there is something new to learn... I love it!

  6. #6
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Macro efficiency - Execute process faster

    Quote Originally Posted by Zoediak View Post
    ... could you further explain or breakdown the code so I can understand how it is working?
    here's an annotated version of that code
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-01-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Macro efficiency - Execute process faster

    Kalak,

    Thank you for the annotations... This will help me out a lot!!

+ 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. efficiency recomendations, code taking long time to execute...
    By am_hawk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2013, 03:55 PM
  2. [SOLVED] Simple Buttons/Macros required to improve efficiency of a process.....
    By Andrew E Smith in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2012, 05:46 PM
  3. Macro simplification & efficiency
    By shapesphere in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-26-2011, 04:12 PM
  4. Macro Efficiency?
    By wz4np1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-14-2010, 07:15 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