How do I select a range of cells that won't stay still!

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.