# returning the last row of a named range

This is a discussion on returning the last row of a named range within the Excel Questions forums, part of the Question Forums category; hi everyone, suppose I hv a named range myRange ("a3:a5"), is there a function to return the last row in ...

1. hi everyone,

suppose I hv a named range myRange ("a3:a5"), is there a function to return the last row in this range, i.e. 5?

thank you very much..

2. Hi Muffins.
Using Excel functions
=ROW(MyRange)+ROWS(MyRange)-1
Using VBA
Range("MyRange").SpecialCells(xlCellTypeLastCell).Row does not work and I don't know why???
This one works though
Range("MyRange").Row + Range("MyRange").Rows.Count - 1

tom

3. Hi muffins:

I am not sure if this is what you are looking for, or if it even helps, but in the following I have a formula to get the last row of myRange

'=COUNTA(myRange)+ROW(myRange)-1

Please post back if it works for you -- otherwise explain a little further and let us take it fom there.

4. On 2002-11-09 02:04, TsTom wrote:
[...]Using Excel functions
=ROW(MyRange)+ROWS(MyRange)-1
[...]
Tom,

That won't work (neither will what Yogi suggests), unless it's just a question of establishing the last row of myRange irrespective of whether the last row is in use.

Otherwise...

If myRange is of numeric type...

=MATCH(9.99999999999999E+307,myRange)

If myRange is of "text" type...

=MATCH(REPT("z",90),myRange)

If myRange is of mixed type...

=MAX(MATCH(9.99999999999999E+307,myRange),MATCH(REPT("z",90),myRange))

Note that you can replace 9.99999999999999E+307 with BigNum in the above formulas by defining BigNum as follows...

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

PS. I believe muffins wants a VBA solution.

[ This Message was edited by: Aladin Akyurek on 2002-11-09 02:48 ]

[ This Message was edited by: Aladin Akyurek on 2002-11-09 02:53 ]

5. Tom,

Did you delete your last post? You shouldn't have. See my edited reply.

6. On 2002-11-09 02:55, Pear wrote:

I think you may have misread the question.
[...]
I guess you missed my edited reply.

[ This Message was edited by: Aladin Akyurek on 2002-11-09 02:58 ]

7. On 2002-11-09 02:58, Aladin Akyurek wrote:
On 2002-11-09 02:55, Pear wrote:

I think you may have misread the question.
[...]
I guess you missed my edited reply.

[ This Message was edited by: Aladin Akyurek on 2002-11-09 02:58 ]

Yes, I did. Have deleted my post.

An alternative to TsTom's VBA solution :-

rw = Range([A1], Range("MyRange")).Rows.Count

8. On 2002-11-09 02:34, Aladin Akyurek wrote:

Note that you can replace 9.99999999999999E+307 with BigNum in the above formulas by defining BigNum as follows...

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Aladin, is there a way to incorporate BigNum so that it is available as a defined name in every workbook?
Ian

9. On 2002-11-09 03:50, inarbeth wrote:

On 2002-11-09 02:34, Aladin Akyurek wrote:

Note that you can replace 9.99999999999999E+307 with BigNum in the above formulas by defining BigNum as follows...

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Aladin, is there a way to incorporate BigNum so that it is available as a defined name in every workbook?
Ian
I believe one way is that you can save a wb which includes just the def of BigNum as PERSONAL.XLS in the directory XLstart. Next time you start Excel, PERSONAL.XLS opens automatically.

I read your post again after having posted the question and saw that you had already answered the question. Was hoping that you missed it so as not to waste your time.
Tom

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•