Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    refering to named ranges in code (XL2000)

    I have a line of code that says:
    <pre>Set fillRange = ActiveSheet.Range(Cells(25, 1), Cells(24 + [returned], [ColCount]))</pre>


    It works fine but I'd like to replace the fixed reference to Cells(25,1) with the named range I've defined for that cell, the idea being that if I insert/delete rows when the named range moves the macro still finds the correct starting cell. Hints and tips on the correct syntax would be appreciated as my attempts so far give errors.

    stuck

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

    Re: refering to named ranges in code (XL2000)

    You can use ActiveSheet.Range("MyRange") to refer to the named cell, and use the Offset function to refer to a cell relative to the named cell.

    Set fillRange = ActiveSheet.Range(ActiveSheet.Range("MyName"), ActiveSheet.Range("MyName").Offset(returned - 1, ColCount - 1))

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to named ranges in code (XL2000)

    Thank you Hans,

    I was getting there, I knew about referring to a named range via Range("MyName") but hadn't twigged to use Offset for the relative reference.

    Ken

Posting Permissions

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