Unknown Rows

[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.

[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)

"Darren" wrote:

> 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.

[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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Darren" <[email protected]> wrote in message
news:[email protected]...
> 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.