- Jul 2, 2004
- 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
Using the Offset Property to Refer to a Range
You've already seen a reference to Offset; the macro recorder used it when we were recording a relative reference. It enables you to manipulate a cell based off the location of the active cell. In this way, you don't have to know the address of a cell.
The syntax for the Offset property is
To affect cell F5 from cell A1, write
or, shorter yet:
The count starts at A1, but does not include A1.
But what if you need to go over only a row or a column, but not both? You don't have to enter both the row and column parameter. If you need to refer to a cell one column over, use one of these:
Both lines mean the same. The choice is yours. Referring to a cell one row up is similar:
Once again, the choice is yours. It is a matter of readability of the code.
Let's say we had a list of produce with totals next to them. Find any total equal to zero and place LOW in the cell next to it. You could do it this way:
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) Rng.Offset(, 1).Value = "LOW"
The LOW totals are quickly noted by the program, as shown in Figure 3.1.
Figure 3.1 Find the produce with the 0 total.
Offsetting isn't only for single cellsit can be used with ranges. You can shift the focus of a range over in the same way you can shift the active cell. The following line refers to B2:D4 (see Figure 3.2):
Figure 3.2 Offsetting a rangeRange("A1:C3"). Offset(1,1). Select.