The problem is not with SpecialCells, it is with COUNTIF(). Even on the
worksheet COUNTIF won't work on dis-joint ranges. That's why the
cell-by-cell approach.

In
http://www.microsoft.com/communities...a-e20c1dc1c7ba

Bob Phillips gave a very clever solution to this.


--
Gary''s Student


"whitehurst" wrote:


Thank you, I figured criteria expressions could be more complicated than
just appending, but I guess that can't be!

Can anybody think of a way to write CountVIf without having to loop
over each cell? I was wanting to use SpecialCells(xlCellTypeVisible)
and then just pass the result range (which would have multiple areas)
into EVALUATE(COUNTIF(newRange, criteria)), but specialcells doesn't
seem to work in UDFs - which sucks.

Thanks.


--
whitehurst
------------------------------------------------------------------------
whitehurst's Profile: http://www.excelforum.com/member.php...o&userid=34544
View this thread: http://www.excelforum.com/showthread...hreadid=550351