Referring to ranges
- By Bill Jelen and Tracy Syrstad
- 3/11/2022
- The Range object
- Referencing ranges in other sheets
- Referencing a range relative to another range
- Using the Cells property to select a range
- Using the Offset property to refer to a range
- Using the Resize property to change the size of a range
- Using the Columns and Rows properties to specify a range
- Using the Union method to join multiple ranges
- Using the Intersect method to create a new range from overlapping ranges
- Using the IsEmpty function to check whether a cell is empty
- Using the CurrentRegion property to select a data range
- Using the Areas collection to return a noncontiguous range
- Referencing tables
- Next steps
Referencing a range relative to another range
Typically, the Range object 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, which makes for unintuitive code. Consider this example:
Range("B5").Range("C3").Select
This code actually selects cell D7. Think about cell C3, which 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 assume that B5 is 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, which is D7.
Again, I consider this coding style to be very unintuitive. This line of code mentions two addresses, and the actual cell selected is neither of these addresses! It seems misleading when you’re trying to read this code.
You might consider using this syntax to refer to a cell relative to the active cell. For example, the following line of code activates the cell three rows down and four columns to the right of the currently active cell:
Selection.Range("E4").Select
I mention this syntax only because the macro recorder uses it. Recall that when you recorded a macro in Chapter 1, “Unleashing the power of Excel with VBA,” with relative references on, the following line was recorded:
ActiveCell.Offset(0, 4).Range("A1").Select
This line found the cell four columns to the right of the active cell, and from there, it selected the cell that would correspond to A1. This is not the easiest way to write code, but it is the way the macro recorder does it.
Although a worksheet is usually the object of the Range property, occasionally, such as during recording, a range may be the property of a range.