returning the last row of a named range

Thanks:  0
Likes:  0

# Thread: returning the last row of a named range

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.

10.
Tom

## User Tag List

#### Posting Permissions

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