- 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
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:
The syntax to affect cell F5 from cell A1 is
Or, shorter yet, write this:
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:
Both lines mean the same, so the choice is yours. Referring to a cell one row up is similar:
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.
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):
Figure 3.2. Offsetting a range: Range("A1:C3").Offset(1,1).Select.