Referring to Cells by Using a Range Object [Excel 2003 VBA Language Reference]

  • Article

If you set an object variable to a Range object, you can easily manipulate the range by using the variable name.

The following procedure creates the object variable myRange and then assigns the variable to range A1:D5 on Sheet1 in the active workbook. Subsequent statements modify properties of the range by substituting the variable name for the range object.

Sub Random()
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:D5")
    myRange.Formula = "=RAND()"
    myRange.Font.Bold = True
End Sub