- 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
Using the Offset property to refer to a range
You’ve already seen a reference to Offset when you recorded a relative reference. Offset enables you to manipulate a cell based on the location of another cell, such as the active cell. Therefore, you do not need to know the address of the cell you want to manipulate.
The syntax for the Offset property is as follows:
For example, the following code affects cell F5 from cell A1:
Or, shorter yet, you can write this:
The count of the rows and columns starts at A1 but does not include A1.
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 the column parameters. To refer to a cell one column over, use one of these lines:
Both of these lines have the same meaning, so the choice is yours. If you use the second line, make sure to include the comma so Excel knows that the 1 refers to the ColumnOffset argument. Referring to a cell one row up is similar:
Once again, you can choose which one to use. It’s a matter of the readability of the code.
Suppose you have a list of produce in column A, with totals next to the produce items in column B. If you want to find any total equal to zero and place LOW in the cell next to it, do this:
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _ LookIn:=xlValues) Rng.Offset(, 1).Value = "LOW"
When used in a Sub and looping through a data set, it would look like this:
Sub FindLow() With Range("B1:B16") Set Rng = .Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) If Not Rng Is Nothing Then firstAddress = Rng.Address Do Rng.Offset(, 1).Value = "LOW" Set Rng = .FindNext(Rng) Loop While Not Rng Is Nothing And Rng.Address <> firstAddress End If End With End Sub
The LOW totals are noted by the program, as shown in Figure 3-1.
FIGURE 3-1 The code puts “LOW” next to the zeros in the data set.
Offsetting isn’t only for single cells; you can use it 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 the original range A1:C3 by one row and one column references a new range, B2:D4.