Home > Articles

Referring to Ranges

This chapter is from the book

This chapter is from the book

Referencing a Range Relative to Another Range

Typically, .Range is a property of a worksheet. It is also possible to have .Range be the property of another range. In this case, the .Range property is relative to the original range! This makes for code that it very unintuitive. Consider this example:


This actually selects cell D7. Think about cell C3. It is located two rows below and two columns to the right of cell A1. The preceding line of code starts at cell B5. If we assumed that B5 were in the A1 position, VBA finds the cell that would be in the C3 position relative to B5. In other words, VBA finds the cell that is two rows below and two columns to the right of B5, and this is D7.

Again, I consider this coding style to be very unintuitive. This line of code mentions two addresses, and the actual cell being selected is neither of these addresses! It seems very misleading when you are trying to read this code.

You might consider using this syntax to refer to a cell relative to the active cell. For example, this line would activate the cell three rows down and four columns to the right of the currently active cell:


Although it is a matter of personal preference, I find the Offset property (discussed later in this chapter) to be far more intuitive.

This syntax is mentioned only because the macro recorder uses it. Remember that back in Chapter 1, "Unleash the Power of Excel with VBA!" when we were recording a macro with Relative References on, the following line was recorded:

ActiveCell.Offset(0, 4).Range("A2").Select

It found the cell four columns to the right of the active cell and from there selected the cell that would correspond to A2. This is not the easiest way to write code, but that's the macro recorder.

Although a worksheet is usually the object of the Range property, on occasion, such as when recording, a Range may be the property of a Range.

  • + Share This
  • 🔖 Save To Your Account