Technically as a good programmer I should have added another "Else"
statement to return 0 if there are no zero values after the data :)
ElseIf r1.Cells(r1.Rows.Count, 1) = "0" Then
For cnt = r1.Rows.Count To 1 Step -1
If r1.Cells(cnt, 1) < 0 Then Exit For
Next
ws.Range("E33") = r1.Cells(r1.Rows.Count) - cnt
Else
ws.Range("E33") = 0
End If

Charles

Die_Another_Day wrote:
NP. We all have our bad days <gr Try this code, Note that it is not
totally debugged:

Sub CellCounter()
Dim ws As Worksheet
Dim r1 As Range
Dim nonZero As Long
Dim cnt As Long
For Each ws In ActiveWorkbook.Sheets
If Left(ws.Name, 3) = "REV" Then
Set r1 = ws.Range("D8:D31")
nonZero = Evaluate(Application.CountIf(r1, "0"))
ws.Range("E32") = nonZero
If nonZero = 0 Then
ws.Range("E33") = r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count, 1) = "0" Then
For cnt = r1.Rows.Count To 1 Step -1
If r1.Cells(cnt, 1) < 0 Then Exit For
Next
ws.Range("E33") = r1.Cells(r1.Rows.Count) - cnt
End If
End If
Next
End Sub


Charles

David wrote:
It's PERFECT...but I feel like an idiot.
The cells are formatted as Accounting so they look like the are "empty", but
they are really 0.
If I change the ELSEIF line to = "0", then I get the number 23, but if the
cells are really empty, I get the correct result 17. (D8:D10 are 0, D11:D14
have numbers, D15:D31 are 0). What am I doing wrong?

I apologize for giving you the wrong information. Your macro works perfectly
on empty cells!!

"Die_Another_Day" wrote:

Sub CellCounter()
Dim ws As Worksheet
Dim r1 As Range
For Each ws in ActiveWorkbook.Sheets
If Left(ws.Name,3) = "REV" Then
Set r1 = ws.Range("D8:D31")
ws.Range("E32") = Evaluate(Application.CountIf(r1,"0"))
If Evaluate(Application.CountBlank(r1)) = r1.Cells.Count Then
ws.Range("E33") = r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count,1) = "" Then
ws.Range("E33") = (r1.Cells(r1.Rows.Count,1).Row - _
r1.Cells(r1.Rows.Count,1).End(xlUp).Row)
End If
End If
Next
End Sub

Glad to be of help.

Charles
David wrote:
Now THAT was impressive...worked perfectly the way you wrote it...but I'm
looking for the result found on each page to be written to cells on each page
instead of a message box with the total.
Using what you've done, I would like the total count of data cells written
to cell E32 and the number of blank cells written to cell E33 on each page
for that page's results. Thanks so much...we're almost there!

"Die_Another_Day" wrote:

Sub CellCounter()
Dim DataCells as Long
Dim BlankCells as Long
Dim ws As Worksheet
Dim r1 As Range
DataCells = 0
BlankCells = 0
For Each ws in ActiveWorkbook.Sheets
If Left(ws.Name,3) = "REV" Then
Set r1 = ws.Range("D8:D31")
DataCells = DataCells + Evaluate(Application.CountIf(r1,"0"))
If Evaluate(Application.CountBlank(r1)) = r1.Cells.Count Then
BlankCells = BlankCells + r1.Cells.Count
ElseIf r1.Cells(r1.Rows.Count,1) = "" Then
BlankCells = BlankCells + (r1.Cells(r1.Rows.Count,1).Row -
_
r1.Cells(r1.Rows.Count,1).End(xlUp).Row)
End If
End If
Next
Msgbox "There a " & DataCells & " data cells and" & _
vbcrlf & BlankCells & " blank cells after the data cells"
End Sub

Hope I got what you were asking.

Charles
David wrote:
I have a fixed range of cells (D8:D31) on over 100 sheets that I need to get
two things:
1. The count of cells with data (0) and
2. The count of empty cells AFTER the cells with data to D31.

Once data is started in any period, there should be no break in the data,
i.e. if sales start in D14 and the last cell with data is D24, all the cells
inbetween will also have data.

Depending on when sales start, there can be empty cells starting at D8 to
when the first sales for a period are entered (these are new locations added
during the year).

Maybe setting a new range based on the first cell that is greater than 0 to
D31, and then doing the count on that range. I have no idea how to code this.
I need to write the results to two cells, but think I can do that part, but
need to to the same thing for all worksheets beginning with REV. The names
would be like REV001, REV002, etc.

Any ideas would be appreciated.