How to speed up writing to cells with VBA??

[Bundi999]

Dear experts,

I am desperately looking for your help. I have a code (below simplified) with around 20 "checks" that need to be done per each line found in the excel worksheet. The problem is that each ''check'' can have different results, therefore I am doubting whether an array would work. I believe the code is slow due to interaction with the worksheet (writing back each cell separately).

Below is a very simplified portion of what I try to do. In reality I loop over 100k rows with 20 criteria to be written in different columns.

Is there any way to speed this up?

Thank you very much for you kind help!

Bundi

Please Login or Register  to view this content.

[torachan]

How is the data initially been written to the sheet ????
See big yellow banner, workbook would be more useful.

[Bundi999]

Thanks torachan, you are right. I have enclosed a very basic function of what I need. Due to very limited cells it seems to run fast, but in reality with more conditions and rows it takes long. If in my real macro I disable all the writing to cells, it runs fast indicating the issue is there.

Thanks in advance for your time!

[torachan]

Had a quick look and seeded with upto 500,000 rows and run timer, it was 20% quicker just using a simple loop instead of arrays.
This is the sort of sorting that @Jindon would apply a 'speed of light' solution to if he has read your posting.
The attached is how I quickly looked at it, clear column 'C' and seed 'A' with 10,000 or 50,000 or 100,000 or 500,000 rows.
Your procedure is still on its original button, my simple loop is on button 'LOOP THRU' both are on a simple timer.

[jindon]

In reality I loop over 100k rows with 20 criteria to be written in different columns.

It will take time...
For the file you uploaded.

Please Login or Register  to view this content.