Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Name VBA (2000)

    Say I have Sheet 1. I name A1:A20 as MyRange.
    1. How do I declare this in VBA?
    2. IF I Insert a row at say, A10, in 'Spreadsheet' mode I suppose MyRange is now referenced as A1:A21. Will this NEW range be automatically applied in VBA? IF NO, how can I make it so?
    3. Will the same apply IF I had inserted a row at A21? I guess not!
    My apologies if these queries are too basic!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Name VBA (2000)

    No need to apologise att all.

    1. Range("A1:A20").Name = "MyRange"

    2. The range will expand to include the new row and so will exten to A21. Once a range name is declared, how it was declared is irrelevant, i.e. once VBA applies the name it is the same as if you manually applied it.

    3. You guess correctly. The range only expands if the row is inserted inside the range.

    Hope the above help.

    Andrew C

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

    Re: Range Name VBA (2000)

    Your question on "How do I declare this in VBA" is not totally clear. Andrew's answer tells you how to assign that name to the range using VBA. If you were asking how to reference the range in VBA, then you need"

    <pre> Range("MyRange").Select
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Name VBA (2000)

    Thank you very, very much. Truly appreciate your response. Targetted right to the point. Thanks also to Legare. Great to have such reassurance!

Posting Permissions

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