Results 1 to 10 of 10

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. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    86

    Default

    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. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    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. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,890

    Default

    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 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,890

    Default

    Tom,

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

    Aladin

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,890

    Default

    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 ]

  7. #7

    Join Date
    Oct 2002
    Posts
    49

    Default

    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


  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    906

    Default


    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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,890

    Default

    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.

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    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


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com