VBA - Count cells that are hidden and have specific value

Question

I'm trying to figure out how I can write a VBA code that counts how many of the cells in my range are hidden AND have a specific value.

I've tried something like this, but it doesn't work:

Sub Count_hidden_ABC()
Dim s As Long
Dim Rg As Range
Set Rg = Worksheets("Sheet1").Range("G8:G255")
s = Application.WorksheetFunction.CountIfs(Rg, "ABC", Rg, SpecialValues(12))

or

Sub Count_hidden_ABC()
Dim s As Long
Dim Rg As Range
Set Rg = Worksheets("Sheet1").Range("G8:G255")
s = Rg.SpecialCells(12).Application.WorksheetFunction.CountIf(Rg, "ABC")

Anyone has a clue how to do this?

Answer 1

Special Cells feat. Areas

• When you are selecting SpecialCells(12) or SpecialCells(xlCellTypeVisible) you are selecting the visible cells not the invisible that you need.
• Don't know exactly what's going on, but it seems like CountIf doesn't work with non-contiguous ranges.
• So you could loop through the ranges in a non-contiguous range using Areas.

The Code

Sub Count_hidden_ABC()

    Dim s As Long
    Dim Rg As Range
    Dim rng As Range
    Set Rg = Worksheets("Sheet1").Range("G8:G255")

    For Each rng In Rg.SpecialCells(12).Areas
        s = s + WorksheetFunction.CountIf(rng, "ABC")
    Next

    s = WorksheetFunction.CountIf(Rg, "ABC") - s
    'Debug.Print s

End Sub