lastrow in formula

[Steve]

Hi everyone. I have a summary block on the top of a worksheet. Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. How can I have a cell in
my Summary section do a direct cell reference on the last row? For
example, when I run the code, the last row of data is in row 45. So
the summary cell wold reference C45. If the last row was on 75, the
summary would reference C75. Thanks!

 

[Mike H]

Hi

You don't tell us whether were looking for text or numeric so

Numeric
=ROW(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))
text
=ROW(INDEX(B:B,MATCH(REPT("z",10),B:B,1),1))

Both of which are arrays so commit with Ctrl+Shift+enter


Mike

 

[Ron Rosenfeld]

Hi everyone. I have a summary block on the top of a worksheet. Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. How can I have a cell in
my Summary section do a direct cell reference on the last row? For
example, when I run the code, the last row of data is in row 45. So
the summary cell wold reference C45. If the last row was on 75, the
summary would reference C75. Thanks!

Do you want the contents of the cell in the last row of data in column C?

If so, you could use this formula:

=LOOKUP(2,1/(LEN(C1:C65535)>0),C1:C65535)

If you want something else, please be more specific about your data layout and
exactly what you want returned.
--ron

 

[ron]

Hi everyone.  I have a summary block on the top of a worksheet.  Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes.  How can I have a cell in
my Summary section do a direct cell reference on the last row?  For
example, when I run the code, the last row of data is in row 45.  So
the summary cell wold reference C45.  If the last row was on 75, the
summary would reference C75.  Thanks!

It depends upon the structure and location of the summary block. If
only data exists in column C and it is contiguous, then something like

=INDIRECT(ADDRESS(COUNTA(C:C),3))

will work. The formula can be adjusted if the summary block also has
information in column C

=INDIRECT(ADDRESS(COUNTA(C10:C65536),3))

The key is that the VBA populated data must be contiguous for this
approach to be successful...ron