- 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
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
Range.Offset(RowOffset, ColumnOffset)
To affect cell F5 from cell A1, write
Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)
or, shorter yet:
Range("A1").Offset(4,5)
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:
Range("A1").Offset(ColumnOffset:=1)
Range("A1").Offset(,1)
Both lines mean the same. The choice is yours. Referring to a cell one row up is similar:
Range("B2").Offset(RowOffset:=-1)
Range("B2").Offset(-1)
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 cells—it 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):
Range("A1:C3").Offset(1,1)
){kind=small}
Figure
3.2 Offsetting a range—Range("A1:C3"). Offset(1,1). Select.