There are several special properties and methods that can be used to navigate ranges on worksheet objects. The most basic of these is the Range property. This property has two variations. The first is used to reference a range directly by its address. In the following example, this syntax is used to enter the value 10 into the range A1:B4 on Sheet1 of the active workbook:
Worksheets("Sheet1").Range("A1:B4").Value = 10
This syntax also accepts a named range as its argument:
Worksheets("Sheet1").Range("Sales").Value = 10
The second syntax references a range by using the upper-left cell and the lower-right cell of the range as arguments:
Worksheets("Sheet1").Range("A1", "B4").Value = 10
This syntax can take as its arguments addresses, range names, or any property or method that returns a valid single-cell range object or address. However, a common mistake when using properties or methods as arguments for the Range property is to not fully qualify the arguments. For example, the code shown below fails if Sheet1 is not the active sheet:
Worksheets("Sheet1").Range(Range("a1"), _
Range("b4")).Value = 10
This is because the Range property defaults to the active sheet if no sheet is provided to qualify it. If Sheet1 is not active at run time, the outer Range property and the inner Range property will be referring to different sheets. The correct way to handle this is shown in the following example:
With Worksheets("Sheet1")
.Range(.Range("a1"), .Range("b4")).Value = 10
End With
Now all the Range properties are qualified with a reference to the same worksheet, so it no longer matters what the active sheet happens to be.
The Offset and Cells methods are used to refer to ranges located some distance away from some starting point. The following example shows how to use each of these methods to get the value of the cell that is two columns to the left and two rows down from the currently active cell:
MsgBox ActiveCell.Offset(2, 2).Value
MsgBox ActiveCell.Cells(3, 3).Value
Both lines of code above refer to the same cell. The difference between the two methods is that the Offset method has a base index of zero and accepts negative numbers as arguments, allowing you to reference ranges above and to the left of the current range. The Cells method has a base index of 1 and will not accept negative numbers as arguments.
The End method allows you to move to the end of the current data range. It mimics the functionality of holding down the END key on the keyboard while pressing one of the directional arrows. The End method takes one of four predefined constants as an argument, corresponding to the four directions in which you can move. These constants are xlUp, xlDown, xlToLeft, and xlToRight. In the following example, the End method is used to get the value of the bottom cell of the column of data that begins with cell A1:
MsgBox Worksheets("Sheet1").Range("a1").End(xlDown).Value
Note, however, that the End method will stop at the cell above the first empty cell it encounters, regardless of whether or not that is truly the last cell. In other words, the End method moves in a specific direction until it finds a blank cell.