+ Reply to Thread
Results 1 to 13 of 13

How to optimize code? / VBA Overflow '6' Error

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Question How to optimize code? / VBA Overflow '6' Error

    I have code that basically compares rows of 5 cells between sheets and then removes duplicates on the master sheet. I get Overflow '6' error, naturally as I have over 50,000 rows. But when I change from Integer to Long and run it, Excel freezes up. I read elsewhere that using For Each instead of using ranges and incrementing is much more efficient. I would appreciate assistance doing that!

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,448

    Re: How to optimize code? / VBA Overflow '6' Error

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: How to optimize code? / VBA Overflow '6' Error

    Yes, I did that but it freezes up when I run the macro. I was just wondering if there was any more efficient way to do this

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,448

    Re: How to optimize code? / VBA Overflow '6' Error

    Load you data in to array and loop through the array. Attach a sample.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,448

    Re: How to optimize code? / VBA Overflow '6' Error

    May be slightly faster, but still slow. You will get an accurate result if you loop back ward.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: How to optimize code? / VBA Overflow '6' Error

    Here's how I would to it... Code seems to be fast :

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: How to optimize code? / VBA Overflow '6' Error

    Quote Originally Posted by GC Excel View Post
    Here's how I would to it... Code seems to be fast :

    Please Login or Register  to view this content.
    This throws an error '9' / subscript out of range for me

    Also, forgot to mention that the data I am working with is strictly numeric.

  8. #8
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: How to optimize code? / VBA Overflow '6' Error

    Works fine on my side...

    At which line does the macro stops?

  9. #9
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: How to optimize code? / VBA Overflow '6' Error

    Quote Originally Posted by GC Excel View Post
    Works fine on my side...

    At which line does the macro stops?
    It breaks here

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: How to optimize code? / VBA Overflow '6' Error

    Ok, I was assuming that both sheets had the same number of lines.
    Try this code... values are also converted to number.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: How to optimize code? / VBA Overflow '6' Error

    Sorry I should have clarified earlier: I have two data sheets of differing row counts, and I want to remove rows that appear in Drawn from Master (i.e. duplicate rows). Now for whatever reason your code isn't working correctly. The string to number conversion was just giving dotted outline and pasting left blanks. Even cutting that part of the code out, it still doesn't remove any duplicates.

    What I had originally (substituted with 'Long') does "work," but for example I have to run it on my Xeon server and it takes a while. I was just thinking that there was a more optimal method of performing the cleaning. Elsewhere someone had posted that it was more efficient to use 'For Each' instead of incrementing each row. I am pretty novice when it comes to VBA so I wasn't sure, that's why I'm here

  12. #12
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: How to optimize code? / VBA Overflow '6' Error

    Ok, my error... From the beginning, I thought you were comparing row by row.

    This should work :

    Please Login or Register  to view this content.
    Note : generally : "For each" are faster then incrementing each row. But Arrays are faster then "For each" when working with range of variables. In this case, using a dictionary is also faster.

    If it does not work, please post a sample file with some rows...

  13. #13
    Registered User
    Join Date
    04-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: How to optimize code? / VBA Overflow '6' Error

    As far as I can tell, that works! And it's definitely faster.

    I was still having issues with your string to number conversion so I just removed

    Please Login or Register  to view this content.
    and used

    Please Login or Register  to view this content.
    instead.


+ 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. runtime error 6 overflow for code can't solve.
    By fequalsma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2013, 11:05 AM
  2. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  3. Optimize VBA code
    By doodlebug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2007, 07:53 AM
  4. Overflow error - same code, different sheet
    By rwnelson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-30-2006, 12:35 PM
  5. Error Code 6: Overflow
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-23-2005, 12:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1