Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

This chapter is from the book

This chapter is from the book

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.

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):

Range("A1:C3").Offset(1,1)
Figure 3.2

Figure 3.2. Offsetting a range: Range("A1:C3").Offset(1,1).Select.

  • + Share This
  • 🔖 Save To Your Account