- The Range Object
- Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
- 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 Quickly Select a Data Range
- Using the Areas Collection to Return a Non-contiguous Range
- Next Steps
This chapter is from the book
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:
Range("B5").Range("C3").Select
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:
Selection.Range("E4").Select
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.