returning the last row of a named range

muffins

Board Regular
Joined
Jun 18, 2002
Messages
86
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..
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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.
 
Upvote 0
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.

Aladin

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
 
Upvote 0
On 2002-11-09 02:55, Pear wrote:
To Aladin Akyurek

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
 
Upvote 0
On 2002-11-09 02:58, Aladin Akyurek wrote:
On 2002-11-09 02:55, Pear wrote:
To Aladin Akyurek

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
 
Upvote 0
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

Aladin, is there a way to incorporate BigNum so that it is available as a defined name in every workbook?
Ian
 
Upvote 0
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

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.
 
Upvote 0
Hi Aladin.
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
:)
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top