How to count only visible cells in a custom function (count cells based on cell colour)

Question

I am using a custom function that I found on the net. It does what I need it to do - counts cells of specific colour in a range of cells.

How do I edit it to only count visible cells? I have tried to use this, but it doesn't doo anything.

For Each rCell In CountRange.SpecialCells(xlCellTypeVisible)

Complete function is this:

    Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

To use it in the worksheet I then type:

=GetColorCount(A1:G20,H1, NOW())

Where:

A1:G20 is the range I want to count all yellow cells
H1 is cell where count is displayed and is coloured in yellow
NOW() makes it run everytime a change is made in the range (??)

Any tips?

I believe `SpecialCells` won't work within a `UDF` – JvdV Feb 07 '20 at 13:24 — JvdV, Feb 07 '20 at 13:24

score 1 · Answer 1 · answered Feb 07 '20 at 13:39

SpecialCells is indeed flaky when wrapped in a UDF like this.

Here is a solution/workaround by checking the row height and column width for zero. I'm sure others exist but this is one way of achieving your goal.

Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
    Dim CountColorValue As Integer
    Dim TotalCount As Integer
    CountColorValue = CountColor.Interior.ColorIndex
    For Each rcell In CountRange.Cells
        If rcell.Interior.ColorIndex = CountColorValue Then
            If (rcell.EntireRow.Height * rcell.EntireColumn.Width) <> 0 Then
                TotalCount = TotalCount + 1
            End If
        End If
    Next rcell
    GetColorCount = TotalCount
End Function

Thx CRL. This works as well as the other answer I received. Hope this will be useful for other members of this group :) — Alina, Feb 07 '20 at 14:49

FaneDuru · Accepted Answer · 2020-02-07T14:07:52.667

1

Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
 Dim ColVal As Long, rCell As Range
 Dim TotalCount As Long
 ColVal = CountColor.Interior.ColorIndex
    For Each rCell In CountRange.Cells
      If rCell.Interior.ColorIndex = ColVal Then
          If rCell.EntireRow.Hidden = False And _
              rCell.EntireColumn.Hidden = False Then
              TotalCount = TotalCount + 1
          End If
      End If
    Next rCell
 GetColorCount = TotalCount
End Function

SpecialCells cannot be used in UDF, but checking of each cell in the range visibility is possible...

edited Feb 07 '20 at 14:07

answered Feb 07 '20 at 13:58

FaneDuru

38,298
4
19
27

Great solution FaneDuru! Works a treat! Thx for taking time to answer – Alina Feb 07 '20 at 14:48
Welcome! But Solutions posted by @CLR should also work. And it is an interesting workaround... Ups. Only now I could see that you confirmed that (in a way) :) – FaneDuru Feb 07 '20 at 14:53
This is basically identical to my earlier answer.. but I'm just wondering why you switch OPs CountColorValue (your ColVal) to a Long? Being a `.Colorindex` it only requires an Integer as it's 0 to 56. – CLR Feb 07 '20 at 14:55
@CLR - but [there's no benefit to using Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Feb 07 '20 at 14:59
No benefit to keeping it Integer, no, but possible confusion to the OP in changing it? When we're lucky enough to actually get an OPs existing code, I try to only change those parts that need it. – CLR Feb 07 '20 at 15:03
@CLR: Just a matter of habit. No any benefit of using Integer, but it may be problematic if you use it instead of Long, in some cases. And yes, I was working to my answer (in my way) and you wore the first posting it... They are similar enough. It is a normal way of solving such a challenge, I think. – FaneDuru Feb 07 '20 at 15:03
@FaneDuru: no worries. – CLR Feb 07 '20 at 15:09
@CLR: I want no worried... English is not my best point and maybe it is good to say that when I said something about "the normal way" I wanted to say that such a problem could have only similar ways/code of solving it. – FaneDuru Feb 07 '20 at 15:11

How to count only visible cells in a custom function (count cells based on cell colour)

2 Answers2