- 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
Use the Offset Property to Refer to a Range
You have already seen a reference to Offset when the macro recorder used it when you recorded a relative reference. Offset enables you to manipulate a cell based off the location of the active cell. In this way, you do not need to know the address of a cell.
The syntax for the Offset property is as follows:
Range.Offset(RowOffset, ColumnOffset)
The syntax to affect cell F5 from cell A1 is
Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)
Or, shorter yet, write this:
Range("A1").Offset(4,5)
The count of the rows and columns 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 the column parameter. If you need to refer to a cell one column over, use one of these lines:
Range("A1").Offset(ColumnOffset:=1)
Range("A1").Offset(,1)
Both lines mean the same, so the choice is yours. 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 them 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"
Used in a sub and looping through a table, 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.