How can I optimize my code

Hi Thank you for your response, with my old code it took about 26 seconds to run and with your adjusment about 21.5 seconds. I'm still positive there's something that can be done much faster than this.

@viettest
Please use code tags around code (the # button) instead of quote tags.I changed them for you this time

Have you tired countifs using tables and structured references? I'd have thought they would be faster than VBA

So right now the table is populated with countifs to fill it out. That specific section maybe takes about 2-3 seconds to calculate but the problem is this worksheet has many tables all filled with mostly countif functions. The whole sheet takes around 25 seconds to calculate and my goal was to cut this time by using vba to populate the tables

I’m not saying it’s impossible to make the process faster with VBA but for formulas as simple as these, I’d bet against it. Excel formulas support multi threading and they’re natively faster than VBA. You can normally make sheets more responsive with VBA due to fewer formulas, to make it faster you usually need a conceptually different solution in VBA and you’re essentially replicating the countifs functions.

I suspect that you can probably do this with get and transform, but I don’t Kobo W it well enough to advise whether it would be any faster

Hi xladept first off thanks alot for your input with my code.

After testing your code it brought the time down to 12.5 seconds! Which is awesome but I'm being very picky about speed. So I played around a bit more and found that using DateAdd was adding considerable time to the speed. I replaced

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

And the time is now at around 4.7-5 seconds. May be this is as good as it will get? I will try some more but your code helped me a lot thanks.