×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Tips and Tricks

How do I select a range of cells that won't stay still! by SimonDavis
Posted: 28 Mar 01

Sometimes I find it useful to use a range name rather than an absolute reference when working on multiple cells.

If I have say cells b4 - c20, and want to work on that data (sort/delete etc), I can refer to that range specifically in my code.

If however I want to change the sheet afterwards, especially inserting rows/columns before the range, the change does not propagate to the code. The same cells will be selected, even though the data has moved.

By using a range name, this problem is avoided. The code will always refer to that area of data, wherever it may be on the sheet.

It also allows me to add or delete lines/columns (if more than one) inside of the range, without having to change the code. The range will automatically extend to include the new data.

To set a range name, highlight the area, click Insert/Name/Define and choose something descriptive.

There are a few words which the system uses, so avoid names like 'date', 'time' or any Excel function name  etc. It might possibly be accepted as a range name, but will not be usable in any VB/Macro code.

The range name will work as a variable in the same way as a cell reference, and is selected in the same way e.g.

  Range.("rangename").select

When working on the sheet you can quickly select the named range by either hitting F5, which will list all the named ranges in the sheet, or selecting it from the range name box which is in the top left, just above the column headers.

Finally, there is no restriction on how many ranges a cell belongs to. You can name one range, then define another name within that range, or overlapping parts of it. Whatever you like.

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close