Range Property [Excel 2003 VBA Language Reference]

Range property as it applies to the AllowEditRange object.

Returns a Range object that represents a subset of the ranges that can be edited edited on a protected worksheet.

expression.Range

expression Required. An expression that returns an AllowEditRange object.

Range property as it applies to the Application, Range, and Worksheet objects.

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

expression.Range(Cell1, Cell2)

expression Required. An expression that returns one of the above objects.

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. You can 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.

Remarks

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, then 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.

Range property as it applies to the ListColumn, ListObject,ListRow, HeaderRowRange, InsertRowRange, and TotalsRowRange objects.

Returns a Range object that represents the range to which the specified list object in the above list applies. Read-Only.

expression.Range

expression Required. An expression that returns one of the above objects.

Range property as it applies to the AutoFilter, Hyperlink, PivotCell, and SmartTag objects.

For an AutoFilter object, returns a Range object that represents the range to which the specified AutoFilter applies. For a Hyperlink object, returns a Range object that represents the range the specified hyperlink is attached to. For a PivotCell object, returns a Range object that represents the range the specified PivotCell applies to. For a SmartTag object, returns a Range object that represents the range the specified smart tag applies to.

expression.Range

expression Required. An expression that returns one of the above objects.

Range property as it applies to the AllowEditRange object.

Returns a Range object that represents a subset of the ranges that can be edited edited on a protected worksheet.

expression.Range

expression Required. An expression that returns an AllowEditRange object.

Range property as it applies to the GroupShapes and Shapes objects.

Returns a ShapeRange object that represents a subset of the shapes in a Shapes collection.

expression.Range(Index)

expression Required. An expression that returns one of the above objects.

Index  Required Variant. The individual shapes to be included in the range. Can be an integer that specifies the index number of the shape, a string that specifies the name of the shape, or an array that contains either integers or strings.

Remarks

Although you can use the Range property to return any number of shapes, it's simpler to use the Item method if you only want to return a single member of the collection. For example, Shapes(1) is simpler than Shapes.Range(1).

To specify an array of integers or strings for Index, you can use the Array function. For example, the following instruction returns two shapes specified by name.

Dim arShapes() As Variant
Dim objRange As Object
arShapes = Array("Oval 4", "Rectangle 5")
Set objRange = ActiveSheet.Shapes.Range(arShapes)

In Microsoft Excel, you cannot use this property to return a ShapeRange object containing all the Shape objects on a worksheet. Instead, use the following code:

Worksheets(1).Shapes.SelectAll        ' select all shapes
set sr = Selection.ShapeRange       ' create ShapeRange

Example

As it applies to the Application, Range, and Worksheet objects.

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. 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 < .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 to italic. The example uses Syntax 2 of the Range property.

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

As it applies to the AutoFilter, Hyperlink, PivotCell, and SmartTag objects.

The following example stores in a variable the address for the AutoFilter applied to the Crew worksheet.

rAddress = Worksheets("Crew").AutoFilter.Range.Address

This example scrolls through the workbook window until the hyperlink range is in the upper-left corner of the active window.

Workbooks(1).Activate
Set hr = ActiveSheet.Hyperlinks(1).Range
ActiveWindow.ScrollRow = hr.Row
ActiveWindow.ScrollColumn = hr.Column

As it applies to the GroupShapes and Shapes objects.

This example sets the fill pattern for shapes one and three on myDocument.

Set myDocument = Worksheets(1)
myDocument.Shapes.Range(Array(1, 3)) _
    .Fill.Patterned msoPatternHorizontalBrick

This example sets the fill pattern for the shapes named "Oval 4" and "Rectangle 5" on myDocument.

Dim arShapes() As Variant
Dim objRange As Object
Set myDocument = Worksheets(1)
arShapes = Array("Oval 4", "Rectangle 5")
Set objRange = myDocument.Shapes.Range(arShapes)
objRange.Fill.Patterned msoPatternHorizontalBrick

This example sets the fill pattern for shape one on myDocument.

Set myDocument = Worksheets(1)
Set myRange = myDocument.Shapes.Range(1)
myRange.Fill.Patterned msoPatternHorizontalBrick

This example creates an array that contains all the AutoShapes on myDocument, uses that array to define a shape range, and then distributes all the shapes in that range horizontally.

Set myDocument = Worksheets(1)
With myDocument.Shapes
    numShapes = .Count
    If numShapes > 1 Then
        numAutoShapes = 1
        ReDim autoShpArray(1 To numShapes)
        For i = 1 To numShapes
            If .Item(i).Type = msoAutoShape Then
                autoShpArray(numAutoShapes) = .Item(i).Name
                numAutoShapes = numAutoShapes + 1
            End If
        Next
        If numAutoShapes > 1 Then
            ReDim Preserve autoShpArray(1 To numAutoShapes)
            Set asRange = .Range(autoShpArray)
            asRange.Distribute msoDistributeHorizontally, False
        End If
    End If
End With

Applies to | AllowEditRange Object | Application Object | AutoFilter Object | GroupShapes Collection Object | Hyperlink Object | ListColumn Object | ListObject Object | ListRow Object | PivotCell Object | Range Collection | Shapes Collection | SmartTag Object | Worksheet Object

See Also | Cells Property