software

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.

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

quot;Darrenquot; wrote:

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

Hi Darren,

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

=COUNTIF($A:$A,quot;lt;gt;quot;)

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, quot;Aquot;).End(xlup).Row

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

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAMquot;Darrenquot; gt; wrote in message
...
gt; How do I create a formula to count a varying number of cells in a row. I
gt; get
gt; a file every month and would like to create, as part of a macro, a formula
gt; that will tell me the number of rows of data. This number would then be
gt; used
gt; for other calculations.