How to loop through massive data with fastest code

[caooi23]

Hi,
I have this code to lookup some values. But number of rows of the data is over 30K, so it takes years to run.
can help to advice what is the correct code to use instead?
Besides, how to set password for the VBA code, so that users will not access to it?

Thank you.

Please Login or Register  to view this content.

[bmouse]

I've seen people add:

Please Login or Register  to view this content.

to speed up things.

[jakopak]

Perheaps the VBA is not best option. One way is to put all data in an array. Array size is limited so the data have to be chopped in smaller chunks by loop. Also when you delete row, in next iteration one row will be skipped. Its better to construct union of range then delete the union at the end.

[JasperD]

Something like this perhaps?

No looping involved.

Please Login or Register  to view this content.

[caooi23]

Hi all,
Is there anyone able to help to advice a dynamic code. I'm also facing the same issue for copypaste the data to same worksheet as below.

Please Login or Register  to view this content.

I'm not in advance for VBA code, your help is much appreciated.

[zbor]

If this is a new question please open new thread.

[caooi23]

sure, but the previous issue not certain if anyone able to help?

[JasperD]

Did you actually try my suggestion... ?

[caooi23]

Hi Jasper,
Yes, I tried, but the formula is not working, I corrected the column etc, it is also not working.