Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find last row of named range using VBA (Excel 2000)

    Is there a way to determine via code what is the last row included in a named range, regardless of whether there is data in the last row or not? For example, if I have a sheet with a named range "Section1" that is defined as A1:C10, how can I determine that the last row is row 10? Even better, that the bottom left corner is cell A10? Perhaps I'm missing something obvious, but I'm not finding an easy way to do this.

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find last row of named range using VBA (Excel 2000)

    Sure:

    <code>
    Dim lRow As Long
    lRow = Range("MyRange").Cells(1, 1).Row + Range("MyRange").Rows.Count - 1
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find last row of named range using VBA (Excel 2000)

    The following expression returns a reference to the bottom left corner of Section1 (assuming it consists of a single contiguous area):

    Range("Section1").Cells(Range("Section1").Rows.Cou nt,1)

    To retrieve the address of this cell:

    Range("Section1").Cells(Range("Section1").Rows.Cou nt,1).Address

  4. #4
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find last row of named range using VBA (Excel 2000)

    Beautiful, that's just what I need. Hans and Legare, thanks to you both.

Posting Permissions

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