Is it possible to speed up this UDF?

Hello! I'm using the UDF below to count specific colours in a range and with visible rows only.

However, I'm using this on a worksheet containing 365 columns with filters and 110 rows (approx 40,000 cells), so it takes around 5-6 seconds to recalculate each time a filter is used on a column.

I think the issue is that the function processes each cell in the specified range but I'm not sure how to do it faster.

There's a section titled 'Faster VBA User-Defined Functions' on the Microsoft website (sorry - can't link it here because this is only my second post!) which talks about assigning a range to a variant that contains an array, and looping on that, but I'm not sure how to accomplish it, or if it's even possible with the code I'm using.

Any help would be greatly appreciated!

Please Login or Register  to view this content.

Hi - I've tried the extra line of code to loop through visible cells only, but it doesn't make any difference to the speed of the calculation.

Many thanks for the reply and suggestion, though!

Taking the idea of skipping hidden rows from Andy Pope's code (Message #5) and combining it with my iterate colored cells only code (from Message #7) should produce the fastest possible color counter. Here is the UDF that does that...

Please Login or Register  to view this content.