- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Use the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
From the author of
From the author of
From the author of
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 are 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
This syntax is mentioned only because the macro recorder uses it. Recall that when you recorded a macro in Chapter 1, “Unleash the Power of Excel with VBA,” with Relative References on, the following line was recorded:
ActiveCell.Offset(0, 4).Range("A2").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 A2. This is not the easiest way to write code, but that 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.