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
---------------
Jacob Skaria


"Ayo" wrote:


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.

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

--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
---------------
Jacob Skaria


"Ayo" wrote:


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.