Last Row Number in a Named Range

[Guest]

I have 3 Named Ranges that change as data is added. Unfortunately, they are
all in Column A.
I need a function that will tell me the row number of the last row with data
in the named range. If I can get one to work, I can do the other two.
One of the range names is MSE and is currently A2:A31. In this example, I'm
looking for a function that will return 31.
Thanks!

 

[Roger Govier]

Hi David

If the data in MSE is text, then
=MATCH(LOOKUP("zzzzz",MSE),MSE,0)
You could use REPT("z"),255) in place of "zzzzz" if that is not enough
to be text that could not be found.
It will of course only return 30, not 31 as there are only 30 elements
in the range

If the data is numeric, you could use
=MATCH(LOOKUP(99^99,MSE),MSE,0)

 

[Dave Peterson]

Do you leave gaps in any of the named ranges? Or do you fill them
consecutively?

If no gaps, you could use this to get the last used cell in a single column
range:
=counta(MSE)+row(MSE)+1

 

[Guest]

That works great on the first Range, but what it is doing is giving the Count
of the number in the range, not the Row Number of the last item in the range.
In the 2nd range for example, it gives 10, but is on row 45.
As the ranges will change...I need the row number of the last cell in the
range. Does that help you help me?
Thanks much!!

 

[Guest]

You da MAN Dave...although I changed the +1 to -1 to get what I was looking
for.
Thank you SO Much!

David

 

[Dave Peterson]

I meant -1 (stupid fingers screwed up!)

Glad you fixed it.

 

[Guest]

This one will work with or without blank cells in between if all the text in
a range are unique

=MATCH(LOOKUP("zzzzz",MSE),A:A,0)