Microsoft Office Tutorials and References
In Depth Information
Working with Range Objects
The third syntax for the Cells property simply returns all cells on the referenced worksheet.
Unlike the other two syntaxes, in this one, the return data isn’t a single cell. This example uses the
ClearContents method on the range returned by using the Cells property on the active
worksheet. The result is that the content of every cell on the worksheet is cleared:
The Offset property
The Offset property, like the Range and Cells properties, also returns a Range object. But
unlike the other two methods that I discussed, the Offset property applies only to a Range
object and no other class. Its syntax is as follows:
The Offset property takes two arguments that correspond to the relative position from the
upper-left cell of the specified Range object. The arguments can be positive (down or to the
right), negative (up or to the left), or zero. The example that follows enters a value of 12 into the
cell directly below the active cell:
ActiveCell.Offset(1,0).Value = 12
The next example enters a value of 15 into the cell directly above the active cell:
ActiveCell.Offset(-1,0).Value = 15
If the active cell is in row 1, the Offset property in the preceding example generates an error
because it can’t return a Range object that doesn’t exist.
The Offset property is quite useful, especially when you use variables within looping
procedures. I discuss these topics in the next chapter.
When you record a macro using the relative reference mode, Excel uses the Offset property to
reference cells relative to the starting position (that is, the active cell when macro recording
begins). For example, I used the macro recorder to generate the following code. I started with
the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.
ActiveCell.FormulaR1C1 = “1”
ActiveCell.FormulaR1C1 = “2”