Help with counting cells in a selected range

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I have a situation here.
1.) I need to select a range of cells,
2.) count the number of cells in the range that contains values and also
3.) count the number of cells in the range that have a RED fill

How do I accomplish this? This is what I have so far:
marketWS.Range("D6:BG" & marketCurrRow - 1).Select
With Selection
cnt = .Cells.Count
End With
marketWS.Range("M1") = cnt

But cnt is counting all the cells in the range("D6:BG" & marketCurrRow - 1)
but I only want to count the cells that have values in them. Any ideas?
Thanks.
 
--Use Worksheet function CountA() as below
Dim rngTemp As Range

Set rngTemp = MARKETWS.Range("D6:BG" & marketcurrrow - 1)
cnt = WorksheetFunction.CountA(rngTemp)
MARKETWS.Range("M1") = cnt

--One way to get the number of red filled cells

For Each cell In rngTemp
If cell.Interior.ColorIndex = 3 Then _
redcount = redcount + 1
Next

If this post helps click Yes
 
Since in any case you have a loop you can check for nonblank cells within the
loop or use COUNTA()

If this post helps click Yes
 
Back
Top