Morefunc

[ken2step]

In the past I used an Add-In called MoreFunc. But with new versions of Excel (2010) I have not been able to get it to work always on others machines. I use the LASTROW function quite often and when I send the file to others of course it will not work.
LASTROW function returns the data in the last cell in a column that is not blank. Is there another function I am not aware of in newer versions of Excel that will do the same thing?
thanks in advance!
Ken

 

[James006]

Hello,

You can test following

=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A100))*(A1:A100<>""))))

HTH

 

[FormR]

Hi, some more options for you to try:

For the last numeric value : =LOOKUP(9.99E+307,A1:A1000)
For the last text value : =LOOKUP(REPT("z",255),A1:A1000)
For the last value regardless : =LOOKUP(2,1/(A1:A1000<>""),A1:A1000)

If you know what type of data you want to retrieve the first two formula are very efficient.

 

[ken2step]

Thanks for the reply James006! That is a new one on me! I'll play with that one.

 

[ken2step]

Thanks for the reply FormR! I've been using your =LOOKUP(9.99E+307,A1:A1000) for those people that use a later version of Excel. The other 2 formulas I had not seen before. The MoreFunc add-in I really like to use and wish they had not stopped supporting it. I hope MS can add it to their product as there are some functions that are more intuitive for me such as LASTROW.
Thanks all for helping me out!
Cheers!

 

[ken2step]

Thanks for the reply FormR! I've been using your =LOOKUP(9.99E+307,A1:A1000) for those people that use a later version of Excel. The other 2 formulas I had not seen before. The MoreFunc add-in I really like to use and wish they had not stopped supporting it. I hope MS can add it to their product as there are some functions that are more intuitive for me such as LASTROW.
Thanks all for helping me out!
Cheers!