Unknown Rows

D

Darren

How do I create a formula to count a varying number of cells in a row. I get
a file every month and would like to create, as part of a macro, a formula
that will tell me the number of rows of data. This number would then be used
for other calculations.
 
D

dlw

use =countif function, the range would be the entire row, like a1:iv1, if
they are numbers, the criteria would be ">0"
(see help screen on countif)
 
Z

Zack Barresse

Hi Darren,

Depending on what type of data you had, you could use something like this
....

=COUNTIF($A:$A,"<>")

If you want to do this in a macro/procedure, you could either use the
Application.WorksheetFunction.CountA, or if you want the last row, something
like this ..

Cells(Rows.Count, "A").End(xlup).Row

The two will function a little differently with non-contiguous data, beware.
See a very detailed write up here:
http://www.xldynamic.com/source/xld.LastValue.html

HTH
 
Top