- The Range Object
- Syntax for Specifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- 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
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 have 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:
Range.Offset(RowOffset, ColumnOffset)
For example, the following code affects cell F5 from cell A1:
Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)
Or, shorter yet, you can write this:
Range("A1").Offset(4,5)
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:
Range("A1").Offset(ColumnOffset:=1)
Range("A1").Offset(,1)
Both of these lines mean the same, 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:
Range("B2").Offset(RowOffset:=-1)
Range("B2").Offset(-1)
Once again, you can choose which one to use. It is a matter of 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.
){kind=link}
Figure 3.1 Find the produce with zero totals.
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):
){kind=link}
Range("A1:C3").Offset(1,1)
Figure 3.2 Offsetting a range: Range("A1:C3").Offset(1,1).Select.