Count Empty Cells in Range After Cells with Data

[Guest]

I have a fixed range of cells (D831) 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.

 

[Guest]

Unless you need to check that filled cells are contiguous:

Sub Getthecount()
Dim rng as Range, sh as Worksheet
for each sh in activeworkbook.Worksheets
if instr(1,sh.name,"rev",vbTextcompare) = 1 then
set rng = sh.Range("D831")
sh.Range("M2").Value = rng.countblank(rng)
sh.Range("M1").Value = rng.count - rng.Countblank(rng)
endif
Next
end sub

Test on a copy of your workbook

You didn't say where to write the results, so I used M1 and M2. Adjust to fit

 

[Die_Another_Day]

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("D831")
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 are: " & DataCells & " data cells and" & _
vbcrlf & BlankCells & " blank cells after the data cells"
End Sub

Hope I got what you were asking.

 

[Guest]

Run-Time Error 428
Object Doesn't Support this property
On the line starting....sh.Range("M2").Value = rng.countblank(rng)

Changed M2 to E32
Changed M1 to E33

 

[Guest]

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]

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("D831")
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.

 

[Guest]

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. (D810 are 0, D1114
have numbers, D1531 are 0). What am I doing wrong?

I apologize for giving you the wrong information. Your macro works perfectly

 

[Die_Another_Day]

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("D831")
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

 

[Guest]

Just a typo, inattention.

Sub Getthecount()

Dim rng as Range, sh as Worksheet
for each sh in activeworkbook.Worksheets
if instr(1,sh.name,"rev",vbTextcompare) = 1 then
set rng = sh.Range("D831")
sh.Range("E32").Value =Application.countblank(rng)
sh.Range("E33").Value = Application.count - Application.Countblank(rng)
endif
Next
end sub

but you have changes your specification now.

 

[Guest]

Charles,
Just a head up.
You don't need to use evaluate with Application.Countif or
Application.CountBlank. These return the number just as they would in the
worksheet. Using evaluate does nothing.

 

[Die_Another_Day]

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]

Tx, just a couple of questions for you Tom, what do I use Evaluate for?
In this routine I was wondering if it is possible to create a range
that is not linked to a specific sheet.
Dim r1 as Range
set r1 = Range("A1:A10")
For Each ws In ActiveWorkbook.Worksheets
if r1....
or is a dimmed range always linked directly to some specific cell?

Thanks,

Charles

P.S. David here is the most updated code:

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("D831")
nonZero = 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
Else
ws.Range("E33") = 0
End If
End If
Next
End Sub

 

[Guest]

A range reference always has a parent. The parent is the sheet on which it
resides.

A string can be use with evaluate since evaluate acts as a virtual cell

mycnt = Evaluate("Countif(D831,"">0"")")

but even in this usage, the D831 is refering to the activesheet.

You use evaluate as a virtual cell on the activesheet. It is mandatory if
you want to do array formulas (or sumproduct used in an array formula
fashion) strictly in VBA because VBA worksheetfunctions don't operate as
array formulas. Evaluate is useful in a few other situations. A bad use of
evaluate is -

Some people like to use

set rng = [A1]
using the shortcut for evaluate.

However, it is silly to go to the Excel application and ask it to evaluate
the string A1 and return a range. This usage is much slower than set rng
= Range("A1")

It isn't all bad - there are some times when the square brackets or evaluate
are more efficient or more useful in a similar context - I don't recall any
good examples at the moment.

 

[Guest]

Tx Tom. Is there anything you don't know 'bout Excel?
--
Charles Chickering

"A good example is twice the value of good advice."

A range reference always has a parent. The parent is the sheet on which it
resides.

A string can be use with evaluate since evaluate acts as a virtual cell

mycnt = Evaluate("Countif(D831,"">0"")")

but even in this usage, the D831 is refering to the activesheet.

You use evaluate as a virtual cell on the activesheet. It is mandatory if
you want to do array formulas (or sumproduct used in an array formula
fashion) strictly in VBA because VBA worksheetfunctions don't operate as
array formulas. Evaluate is useful in a few other situations. A bad use of
evaluate is -

Some people like to use

set rng = [A1]
using the shortcut for evaluate.

However, it is silly to go to the Excel application and ask it to evaluate
the string A1 and return a range. This usage is much slower than set rng
= Range("A1")

It isn't all bad - there are some times when the square brackets or evaluate
are more efficient or more useful in a similar context - I don't recall any
good examples at the moment.


--
regards,
Tom Ogilvy

Tx, just a couple of questions for you Tom, what do I use Evaluate for?
In this routine I was wondering if it is possible to create a range
that is not linked to a specific sheet.
Dim r1 as Range
set r1 = Range("A1:A10")
For Each ws In ActiveWorkbook.Worksheets
if r1....
or is a dimmed range always linked directly to some specific cell?

Thanks,

Charles

P.S. David here is the most updated code:

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("D831")
nonZero = 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
Else
ws.Range("E33") = 0
End If
End If
Next
End Sub

 

[Guest]

Sure - a lot of things and I wouldn't claim to be right all the time either.
Excel has a multitude of capabilities.

--
Regards,
Tom Ogilvy

Tx Tom. Is there anything you don't know 'bout Excel?
--
Charles Chickering

"A good example is twice the value of good advice."

A range reference always has a parent. The parent is the sheet on which it
resides.

A string can be use with evaluate since evaluate acts as a virtual cell

mycnt = Evaluate("Countif(D831,"">0"")")

but even in this usage, the D831 is refering to the activesheet.

You use evaluate as a virtual cell on the activesheet. It is mandatory if
you want to do array formulas (or sumproduct used in an array formula
fashion) strictly in VBA because VBA worksheetfunctions don't operate as
array formulas. Evaluate is useful in a few other situations. A bad use of
evaluate is -

Some people like to use

set rng = [A1]
using the shortcut for evaluate.

However, it is silly to go to the Excel application and ask it to evaluate
the string A1 and return a range. This usage is much slower than set rng
= Range("A1")

It isn't all bad - there are some times when the square brackets or evaluate
are more efficient or more useful in a similar context - I don't recall any
good examples at the moment.


--
regards,
Tom Ogilvy

Tx, just a couple of questions for you Tom, what do I use Evaluate for?
In this routine I was wondering if it is possible to create a range
that is not linked to a specific sheet.
Dim r1 as Range
set r1 = Range("A1:A10")
For Each ws In ActiveWorkbook.Worksheets
if r1....
or is a dimmed range always linked directly to some specific cell?

Thanks,

Charles

P.S. David here is the most updated code:

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("D831")
nonZero = 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
Else
ws.Range("E33") = 0
End If
End If
Next
End Sub

Tom Ogilvy wrote:
Charles,
Just a head up.
You don't need to use evaluate with Application.Countif or
Application.CountBlank. These return the number just as they would in the
worksheet. Using evaluate does nothing.

--
Regards,
Tom Ogilvy


:

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("D831")
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!

:

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("D831")
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 are: " & 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 (D831) 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.

 

[Guest]

Wow...I feel like I'm in the presence of greatness!!

I ran the code and got the value of -7 in cell E33.

I know the answer is 17...so I suspect the code is trying to count the total
rows, which would be 24 and then subtract 7 to get to 17, but it looks like
it's missing the 24 and using 0 - 7 to get -7.

David

 

[Die_Another_Day]

Doh! Programmer error...

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("D831")
nonZero = 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.Rows.Count - cnt 'This is where the
problem was
Else
ws.Range("E33") = 0
End If
End If
Next
End Sub

Let me know if we finally have it working...