Worksheet.Range property

Returns a Range object that represents a cell or a range of cells.

Syntax

expression.Range (Cell1, Cell2)

expression A variable that represents a Worksheet object.

Parameters

Remarks

Cell1 and Cell2 can be A1-style references in the language of the macro. The range references can include the range operator (a colon), intersection operator (a space), or union operator (a comma). They can also include dollar signs, which are ignored. A local defined name can be a range reference. If you use a name, the name is assumed to be in the language of the macro.

Cell1 and Cell2 can be Range objects that contain a single cell, column, row, or any other range of cells.

Often, Cell1 and Cell2 are single cells in the upper-left and lower-right corners of the range returned.

When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn't a worksheet, the property fails).

When applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, Selection.Range("B1") returns cell D3 because it is relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

Examples

This example sets the value of cell A1 on Sheet1 to 3.14159.

Worksheets("Sheet1").Range("A1").Value = 3.14159

This example creates a formula in cell A1 on Sheet1.

Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

This example loops on cells A1:D10 on Sheet1 of the active workbook. If one of the cells has a value less than 0.001, the code replaces that value with 0 (zero).

For Each C In Worksheets("Sheet1").Range("A1:D10")
    If C.Value < 0.001 Then
        C.Value = 0
    End If
Next C

This example loops on the range named TestRange and displays the number of empty cells in the range.

numBlanks = 0
For Each C In Range("TestRange")
    If C.Value = "" Then
        numBlanks = numBlanks + 1
    End If
Next C
MsgBox "There are " & numBlanks & " empty cells in this range"

This example sets the font style in cells A1:C5 on Sheet1 of the active workbook to italic. The example uses Syntax 2 of the Range property.

With Worksheets("Sheet1")
    .Range(.Cells(1, 1), .Cells(5, 3)).Font.Italic = True
End With

This example compares the Worksheet.Range property, the Application.Union method, and the Application.Intersect method.

Range("A1:A10").Select                            'Selects cells A1 to A10.
Range(Range("A1"), Range("A10")).Select           'Selects cells A1 to A10.
Range("A1, A10").Select                           'Selects cells A1 and A10.
Union(Range("A1"), Range("A10")).Select           'Selects cells A1 and A10.
Range("A1:A5 A5:A10").Select                      'Selects cell A5.
Intersect(Range("A1:A5"), Range("A5:A10")).Select 'Selects cell A5.