Solved: Convert Formula to Return a Row

[Cyberdude]

The following formula will return the address of the last used value in array "A1:A500". It?s an array formula, so CTL+SHIFT+ENTER must be used to enter it into a cell.

=ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A))

What change must I make to the formula so it will to return the row instead of the address?

[Bob Phillips]

=MAX(IF(ISBLANK(A1:A500),0,ROW(A1:A500)))

also an array formula.

[Ken Puls]

That's pretty concise!

I was curious if I could extract the number portion using my regular Right(Text,Len(Text)-Find(Character,Text,Start)) method with your array, which does work (array entered):

=RIGHT(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A)),
LEN(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),
COLUMN(A:A)))-FIND("$",ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),
COLUMN(A:A)),2))

I'd definitely go Bob's route, though, but thought I'd share my curiosity. :)

EDIT: Had to break up the formula to prevent hugely wide post.

[Bob Phillips]

Had to break up the formula to prevent hugely wide post.

For info Ken, I tend to enter big formulae in that style in a worksheet as it makes it easier to read and to debug.

[Ken Puls]

For info Ken, I tend to enter big formulae in that style in a worksheet as it makes it easier to read and to debug.

Hey! Cool tip! :) Something that had never occured to me to be honest. That does make it easier to read. :yes

[Cyberdude]

Thanx, Dennis! Nice terse solution. http://vbaexpress.com/forum/images/smilies/friendship.gif

[Bob Phillips]

Thanx, Dennis! Nice terse solution. http://vbaexpress.com/forum/images/smilies/friendship.gif

Who is Dennis?

[Cyberdude]

Oops! .... Terribly sorry, xld. http://vbaexpress.com/forum/images/smilies/ohhmygosh.gif Somewhere along the way I thought I learned that your name is Dennis. Guess not.

[Bob Phillips]

Oops! .... Terribly sorry, xld. http://vbaexpress.com/forum/images/smilies/ohhmygosh.gif Somewhere along the way I thought I learned that your name is Dennis. Guess not.

You are confusing xld, me - Bob, with XL-Dennis, Dennis.

[Zack Barresse]

Bob - Dennis, it's easy to get the two mixed up. ;)

[geekgirlau]

Hey! Cool tip! :) Something that had never occured to me to be honest. That does make it easier to read. :yes

Wow, you learn something new every day! Great tip :thumb

[Bob Phillips]

Bob - Dennis, it's easy to get the two mixed up. ;)

I take that as a huge compliment http://vbaexpress.com/forum/images/smilies/001.gif