VBA Code causes error: “There isn't enough memory to complete this action.”

have a consolidator tool that consolidates data from different worksheets. It can handle up to 1 million rows. However, when I click the button to check duplicates, there's an error that says "There isn't enough memory to do this action." I noticed that this error only happens when this macro runs. Please excuse the bad practice code as I am new to programming and this is what currently works right now. This works for less than 100 rows but when it starts to reach 100+, it ends up freezing and racks up memory. Is there anyway I can clean this code properly while still maintaining the functionality? T

This is how it works:

| Employee ID | Status |

E100 Deactivated

E100 Activated


Turns into:

| Employee ID | Status | Status |

E100 Deactivated Activated

Please Login or Register  to view this content.

I am not sure why it would be sucking up memory, but I see a couple of things that could cause long execution times.

Are you using 32-bit or 64-bit Office?

Could you provide an Excel file with a couple of dozen lines of sample data (not private data) so we can analyze the code under execution?

Your code seems reasonably straightforward although there are a couple of things I would do differently.

Please Login or Register  to view this content.

There is no reason to put an arbitrary number in there. I would use

Please Login or Register  to view this content.

It's not clear what you are trying to do here. Your comment doesn't match the code. The code iterates from columns A through ALL. Iterating

Please Login or Register  to view this content.

Might be better to iterate through the columns that are actually used.

Originally Posted by 6StringJazzer

Are you using 32-bit or 64-bit Office?

I'm using 32-bit Office (sadly).

Originally Posted by 6StringJazzer

Could you provide an Excel file with a couple of dozen lines of sample data (not private data) so we can analyze the code under execution?

Hi, I have attached an excel file here ufile.io/suo2gaxe. Sorry if it's not in url form, the attachment button doesn't work for some reason. Please do change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

for testing.

Originally Posted by 6StringJazzer

There is no reason to put an arbitrary number in there. I would use

Please Login or Register  to view this content.

I decided to put .Rows in replacement for it but it gives out a "mismatch" error.

Originally Posted by 6StringJazzer

It's not clear what you are trying to do here. Your comment doesn't match the code. The code iterates from columns A through ALL. Iterating

Please Login or Register  to view this content.

Might be better to iterate through the columns that are actually used.

The original code was "For i = 1 To 16" (which I found on another side), he has 16 columns so I assume I just have to change that part to how many columns I can predict. Can I use UsedRange here? I'm not quite well versed in VBA so I tried to avoid Ranges.