Shapes.Range property (Excel)

Article
03/30/2022

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

Syntax

expression.Range (Index)

expression A variable that represents a Shapes object.

Parameters

Name	Required/Optional	Data type	Description
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

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

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Share via