Methods to speed up VBA long loops in Excel

Edit 2015-03-02: Thank you all for all these comments and appreciate the suggestions, not that I do not appreciate them, just want you all know that this is not a question, I am not asking for help, I am only stating the findings that I landed on. thanks again.

This is the second time, the first time was back in 2003 when I was creating some project that has long loops, and it needed me few days to discover that the best way to handle these long loops is to combine between Excel cell functions and VBA WorksheetFunction

You see in case you are struggling here
to go through each row in a sheet, we can one of the following:

1- Create a loop that goes through each row 1 by 1
2- Have Excel cell formulas in new column to do the job, this could have multiple IFs, CountIF, etc
For a while I was convinced that VBA loops (1 above) are way faster than Excel functions (2 above), due to the fact that VBA goes directly to compiler VS formulas are calculated.

Then I discovered WorksheetFunction library, which was a great discovery, since then I never stopped using it, it is great and fast
Then I am back again now, facing huge amount of data (more than 600k of rows), where my Worksheetfunction method took around 5 hours
I admit, the formula I used in WorksheetFunction was also big, it was the new one CountIFs, but 5 hours is just too much for 1 sheet out of 20 or even 30 sheets to go.
So, I went back to Excel sheet formulas, but that was not helpful either, it took around 2 hours per sheet using same CountIFs
Then, my final approach is to combine between them and get rid of CountIFs since it was the suspect here
And voila, it worked perfectly, 30 minutes per sheet, that is acceptable

What seams like the best solution is to first use the function in cell first, type in the formula inside these 600k cells (assuming it is small and simple ones), then use CountIF in VBA WorksheetFunction to get the job of CountIFs, CountIF is way faster.

You may ask, so where did I do the conditions I used in CountIFs?
Answer in creating the ID based on my condition, that ID is the new column having Excel cell functions, of course I needed to flatten the formulas (converting them to cell entries) before continue

Even with that, it was fast.
Now I can go home knowing that there is a smile on my client's face.

Posted on Linked in here
https://www.linkedin.com/pulse/methods-speed-up-vba-long-loops-excel-anmar-amdeen