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.

Please Login or Register  to view this content.

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

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

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.

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

Please Login or Register  to view this content.

Originally Posted by GC Excel

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.

Works fine on my side...

At which line does the macro stops?

Originally Posted by GC Excel

Works fine on my side...

At which line does the macro stops?

It breaks here

Please Login or Register  to view this content.

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.

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

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...

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.