Populated Cells In A Named Range

B

Bob Barnes

I am an Access Programmer using automation to Excel

In a Named Range of 12 cells, is there a function to tel
how many of those cells have entries greater than Zero

I could code that w/ many IF statements, but would lik
to know if a function exists

TIA - Bob
 
J

Jim Rech

In a worksheet that would be accomplished with a formula like this:

=COUNTIF(RangeName,">0")

So presumably

xlApp.CountIf(XL.Range("RangeName"), ">0")

would return the count via automation.
--
Jim Rech
Excel MVP
|I am an Access Programmer using automation to Excel.
|
| In a Named Range of 12 cells, is there a function to tell
| how many of those cells have entries greater than Zero?
|
| I could code that w/ many IF statements, but would like
| to know if a function exists.
|
| TIA - Bob
 
W

William Ryan eMVP

You can invoke the CountIF function like this:
=COUNTIF(RangeName,">0")
 
O

onedaywhen

Bob Barnes said:
|I am an Access Programmer using automation to Excel.
|
| In a Named Range of 12 cells, is there a function to tell
| how many of those cells have entries greater than Zero?
|
Click to expand...

As a MS Access developer you will be please to learn that you don't
need automation to access read cell values, you can access the names
range as if it were a Jet table. This is how the query would look like
from a MS Access query's SQL window:

SELECT
Count(MyKeyColumn)
FROM
[Excel 8.0;database=C:\MyWorkbook.xls;].[MyNamedRange]
GROUP BY
MyKeyColumn
HAVING
INT(MyKeyColumn) > 0
;

--
 
Top