Using CountIf in VBA with visible cells

Question

I am trying to use a CountIf function in vba on visible cells to count all the visible cells that are yes, there are 25 but I get the error

Unable to get the CountIf property of the WorksheetFunction class

and it highlights returnCount, not sure if there is also an error with myrange, any help would be much appreciated.

Set myrange = _
Range("D4",Range("D4").End(xlDown)).SpecialCells(xlCellTypeVisible)

returnCount = WorksheetFunction.CountIf(myrange, "yes")

Answer 1

COUNTIF does not like non contiguous or multi-range ranges. So iterate the areas in the range

Dim myrange As Range
Dim ar As Range
Set myrange = _
Range("D4", Range("D4").End(xlDown)).SpecialCells(xlCellTypeVisible)
For Each ar In myrange.Areas
    returncount = returncount + Application.WorksheetFunction.CountIf(ar, "yes")
Next ar