Skip to main content

Range.Range property

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


expression.Range (Cell1, Cell2)

expression A variable that represents a Range object.


Name Required/Optional Data type Description
Cell1 Required Variant The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they're ignored.

Use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.

Cell2 Optional Variant The cell in the upper-left and lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.


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's relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.


This example sets the value of the top-left cell of the range B2:C4 on Sheet1 of the active workbook, i.e. that of the cell B2, to 3.14159.

With Worksheets("Sheet1").Range("B2:C4")
    .Range("A1").Value = 3.14159
End With

This example loops on the the four cells in the top-left corner of the range B2:Z22 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).

Public Sub TruncateSmallValues()
    Dim exampleRange As Excel.Range
    Set exampleRange = Worksheets("Sheet1").Range("B2:Z22")
    Dim cell As Excel.Range
    For Each cell In exampleRange.Range("A1:B2")
    If cell.Value < 0.001 Then
        cell.Value = 0
    End If
    Next cell
End Sub

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

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

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>