Quickly count used rows?
 

Suppose I have a 60 x 9 cell range that is initially empty.
Users will be able to fill data into each row. Is there a
quick and efficient method to count how many rows
contain data?? (A row is considered to contain data if
any one more cells of the 9 columns of that row contain
any type of data)

IMPORTANT:

I would like to be able to retrieve the count of used rows every
time data is added or deleted from the 60x9 cell range.

Also, I'm looking for a FAST way to count the number of rows that
contain data. I already know that I can scan each row one at a time
and get the count, but I'm interested in a faster technique since my
table will likely be much bigger than 60 rows. Isn't there a faster
scheme that handles the "Worksheet_Change" event to retrieve
the count of used rows in a range??

I'd appreciate any innovative ideas here. Thank you!

Robert

Quickly count used rows?
 

Hi Rob

Assuming your cell Range Is A2 to I61 then

If the range is populated with Alhpa characters, then
=COUNTIF($A$2:$I$61,"""")


If the rnage is populated with Numeric, then
=COUNTIF(A2:I61, "0")

or, if there is a mix of Alpha-Numerics
=COUNTA($A$2:$I$61,"""")

HTH
Mick.

Quickly count used rows?
 

Il 18/09/2012 10:48, Robert Crandal ha scritto:
Suppose I have a 60 x 9 cell range that is initially empty.
Users will be able to fill data into each row. Is there a
quick and efficient method to count how many rows
contain data?? (A row is considered to contain data if
any one more cells of the 9 columns of that row contain
any type of data)

IMPORTANT:

I would like to be able to retrieve the count of used rows every
time data is added or deleted from the 60x9 cell range.

Also, I'm looking for a FAST way to count the number of rows that
contain data. I already know that I can scan each row one at a time
and get the count, but I'm interested in a faster technique since my
table will likely be much bigger than 60 rows. Isn't there a faster
scheme that handles the "Worksheet_Change" event to retrieve
the count of used rows in a range??

I'd appreciate any innovative ideas here. Thank you!

Robert




=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:I1,ROW(1:60)-1,))0))

Is this enough FAST?
E.

Quickly count used rows?
 

That works for ONE row. My sheet has 60 rows and 9 columns.
Is this forumula missing something?


"plinius" wrote in message
...


=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:I1,ROW(1:60)-1,))0))

Is this enough FAST?
E.

Quickly count used rows?
 

On second test, your formula does work. Interesting solution!

Thank you!


"Robert Crandal" wrote in message
...
That works for ONE row. My sheet has 60 rows and 9 columns.
Is this forumula missing something?


"plinius" wrote in message
...


=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:I1,ROW(1:60)-1,))0))

Is this enough FAST?
E.

Quickly count used rows?
 

Il 18/09/2012 21:18, Robert Crandal ha scritto:
On second test, your formula does work. Interesting solution!

Thank you!



Glad to help. Thanks for the feedback ;-)