Rewrite code to use less memory

[Karl Gustaf Karsten]

I posted this problem a few days ago, here.
Now that I understand the problem a little better, I’m posting it again (hopefully a little clearer)

I have a macro that performes a simple task. It matches rows from sheet2 with rows in sheet1 if their respective value in column A match.
However, when I have a lot of data, I get a memory error and can't run the code.

This is an example of a smal dataset (the line that causes the error):

Please Login or Register  to view this content.

My question: Is there a way to break this up into several separate operations?
E.g. first find matches for row 1 and 2, then row 3 and 4, etc.

I’m stuck, so I appreciate all feedback!

[InvisibleMan]

That code extract works for me and generates

Please Login or Register  to view this content.

in cell I1, copied across and down to cell M10.

[xladept]

Hi Karl,

Try this:

Please Login or Register  to view this content.

[Karl Gustaf Karsten]

Wow!
That looks great, tested it now and I think it's exactly what I'm looking for (and much faster than what I had)
Thank you very much, Xladept

My sheets may have more rows and columns, is there any reason why this is not a good way to generalize it?

Please Login or Register  to view this content.

[xladept]

Hi Karl,

I kept the 1's with thr 1's and the 2's with the 2's and got rid of that Column letter:

Please Login or Register  to view this content.

[Karl Gustaf Karsten]

Perfect! This is beautiful

Thanks again, this is very very helpful!

[xladept]

You're welcome and thanks for the rep!

[xladept]

Hi Karl,

Here's a quicker version for your big file:

Please Login or Register  to view this content.

[Karl Gustaf Karsten]

Hi, thanks again!

I'm sure it's faster but I get a memory error on this line (on a laptop)

Please Login or Register  to view this content.

However, you've already reduced my run time from hours to seconds - And I'm very exited about that

I'll try it again tomorrow on a desktop.

[xladept]

Hi Karl,

Thanks for the rep!

How big are your worksheets?

Let me know - I wonder if the Array size is limited?

[xladept]

Hi Karl,

If you need to rerun it, you'll want this code (or the quick code):

Please Login or Register  to view this content.

[Karl Gustaf Karsten]

Hi again,

Sheet1 has 140 000 rows and goes to column DA
Sheet2 has 250 000 rows and goes to column AK

I might need to work with bigger sheets in the future, but not significantly bigger, so I think this will do it

I ran the quick code again and got the out of memory error, here:

Please Login or Register  to view this content.

I timed Sub KarlK() and Sub KarlKLG() and both used 22 seconds, is the latter better?

[xladept]

The only difference in the two routines is that you can run the second one over and over - the first one would keep growing the LC1!

* I wonder if your configuration is adequate? Do you have your core partitioned or are you short on RAM? I've processed larger books than that - I have 4GB of RAM and 367GB of free space.

** but 22 seconds? that code certainly fulfills the requirement

[Karl Gustaf Karsten]

Ok, thats good to know.

I'm not sure about the configuration at all - My (work) computer has access to my partition (40GB free) through a network, and I do not have access to the C: drive. System information says 8GB of RAM though.

Anyway, thanks again for taking the time to help!
It makes this project a lot easier for me so I really appreciate it

[xladept]

Well - the "slow" program satisfies the "use less memory" criterion - glad it worked out