Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lastrow in formula
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lastrow in formula
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 "Steve" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lastrow in formula
On Wed, 26 Mar 2008 13:50:33 -0700 (PDT), Steve
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lastrow in formula
On Mar 26, 2:50*pm, Steve wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lastrow | Excel Worksheet Functions | |||
Lastrow | Excel Worksheet Functions | |||
Using the Lastrow variable in a Count/Countif formula | Excel Programming | |||
Go to lastrow using other column's lastrow | Excel Programming | |||
Help with LastRow | Excel Programming |