CODEWITHSUNDEEP
excelvbahiddenvisible
thedeepfield
thedeepfield

Reputation: 6196

VBA: How to ignore hidden rows in range?

I am trying to do a count of all rows that are highlighted and are visible (not hidden). My count formula works but it is still counting hidden rows that also happen to be hidden. How can I count only highlighted and visible rows?

'This function will count how many cells in a given range for a given color and are visible

Function COUNTCELLCOLORSIF(CellRange As Range) As Long

 Dim rngCell

 Application.Volatile

 For Each rngCell In CellRange
    If rngCell.Interior.ColorIndex = "36" and rngCell.visible Then
       COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
    End If
 Next rngCell

End Function

Upvotes: 0

Views: 7083

Answers (2)

Makah
Makah

Reputation: 4513

Try something like this:

Function COUNTCELLCOLORSIF(CellRange As Range) As Long
 Dim rngCell, visibleCells

 Application.Volatile
 visibleCells = CellRange.SpecialCells(xlCellTypeVisible) 

 For Each rngCell In visibleCells
    If rngCell.Interior.ColorIndex = "36" and rngCell.visible Then
       COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
    End If
 Next rngCell

End Function

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Use specialcells(xlcelltypevisible)

Function COUNTCELLCOLORSIF(CellRange As Range) As Long

 Dim rngCell

 Application.Volatile

 For Each rngCell In CellRange.specialcells(xlcelltypevisible)
    If rngCell.Interior.ColorIndex = "36" Then
       COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
    End If
 Next rngCell

End Function

Upvotes: 1

Related Questions