Developer’s Guide to the Excel 2010 Range Object

Many of the most common tasks that you do using the Excel object model involve working with ranges of cell. The Range object represents one or more cells, and can be used to represent a single cell, a row, a column, a selection of cells that contain one or more contiguous blocks of cells, or a 3-D range.

This Visual How To shows how to perform the following tasks:

• Insert values into a range

• Refer to range

• Select ranges

• Combine and intersect ranges

• Use named ranges

• Copy and move ranges

• Loop through ranges

• Search within a range

This Visual How To is based on Developers Guide to the Excel 2007 Range Object by Frank Rice. This article contains additional information and code samples, and generally the Excel 2007 information is valid for Excel 2010.

The Range property returns a Range object. The syntax of the Range property can have the following two forms.

expression.Range(cell1)

expression.Range(cell1, cell2)

In the first form, cell1 identifies the range of one cell; in the second form, the cell1 and cell2 values represent the range of contiguous cells. In these examples, expression is a required element that must be an Application object, a Range object, or a Worksheet object (or an expression that returns one of these objects). If it is omitted, it is assumed to be the ActiveSheet object. In the second example, the cell separator can be the range operator (the colon), the intersection operator (a space), or the union operator (a comma).

In the following sections, you will learn about many of the operations that you can do with Excel ranges. However, these examples are only a subset of the things that you can accomplish with ranges. For more information about the Range Object Model members, see Range Object Members.

Inserting Values into a Range

Inserting values into a single cell in a range is straightforward. The following example inserts a value of 3.14159 into cell A1.

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

Similarly, you can use a named cell instead of A1. A named range has a unique name, exposes events, and can be bound to data. Named ranges are a powerful tool in Excel that enables you to assign a meaningful name to a single cell or a range of cells. For example, you can assign the name "SalesTax" to cell C1 and then use the name "SalesTax" whenever you would normally use the cell C1.

Worksheets("Sheet1").Range("SalesTax").Value = 0.095

The following example inserts a single value into a range of cells.

Worksheets("Sheet1").Range("A1:B10").Value = 1

Using the second form of the Range property described earlier in this section, you can accomplish the same result with the following line of code. Be aware that the object reference is omitted so the active sheet is assumed. Also, because a property for the Range object has been omitted, the Value property is assumed and assigned the value 1.

Range("A1", "B10") = 1

Again, by using the second form, the following statement puts the value "XYZ" in cells A1, A3, and A5 on Sheet2.

Worksheets("Sheet2").Range("A1, A3, A5") = "XYZ"

The syntax is similar for formulas. The following code example sets the formula for A1 equal to random value from 0 to 10.

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

The following example loops through cells D1:D10 on Sheet1. If one of the cells has a String value equal to "For Sale", the code replaces that value with the string "Sold".

For Each c in Worksheets("Sheet1").Range("D1:D10")
   If c.Value = "For Sale" Then 
      c.Value = "Sold"
   End If 
Next c

Referring to Ranges

There are a several ways to refer to a range:

• By cell address

• As an offset from another cell

• By name

• In the current selection

There are two ways to refer to a range directly by using cell addresses: you can use absolute references by using the ActiveSheet object, or you can use relative references by using the ActiveCell object or other range object.

Referring to Ranges on Worksheets

Typically, you refer to a range on the active worksheet. However, you can easily refer to a range in an inactive workbook. For example, you refer to a range on the ActiveSheet by using the Range property as in the following, which selects the cells B1 through B15.

Range("B1:B15").Select

To refer to the same range of cells in a worksheet other than the active worksheet, you specify the index or name of the worksheet.

Worksheets(2). Range("B1:B15").Select

You can also refer to a range on an inactive worksheet without specifying the worksheet, but only if the range has a unique name. This technique works regardless of which worksheet contains the named range is located.

Range("SalesTax").Select

Referring to Individual Cells in a Range

To refer to individual ranges within a range, be aware that they are referenced from the upper-left corner. So if we define a range to be B4 to E10, then cell A1 within that range is actually B4, and cell B2 within that range is C5, as shown in the following code example.

Dim myRange As Range
Set myRange = Range("B4:E10")
myRange.Range("A1").Value = 100000
myRange.Range("B2").Value = 200000

Referring to Cells in a Range by Using Offsets

The Offset property returns a Range object and enables you to refer to a cell in relation to another cell by using the following syntax.

object.Offset(rowOffSet, columnOffset)

By specifying the number of columns and rows that you are offset from a specific location, you can refer to the cell. Positive numbers move the focus down and to the right. Negative numbers move the focus up and left. Zero refers to the current cell.

For example, assume that E5 is the active cell. This statement places a 1 one row under E5 into cell E6.

ActiveCell.Offset(1,0) = 1

This statement places a 1 one column to the right of E5 into cell F5.

ActiveCell.Offset(0,1) = 1

This statement places a 1 three columns to the left of E5 into cell B5.

ActiveCell.Offset(0,-3) = 1

The following code example calculates a moving average by using the Offset property. The code works by setting the range as the first three values in the B column, dividing the sum of those values by 3, and then inserting the average into the C3 cell. Next, the Offset property moves the range down one row, still in the B column, and calculates the average of the values in the range B2:B4. This value is inserted in the C4 cell. This process is repeated through the B12 cell.

Dim rng As Range
Dim lngRow As Long
Set rng = Range("B1:B3")
    
For lngRow = 3 To 12
    Cells(lngRow, "C").Value = WorksheetFunction.Sum(rng) / 3
    Set rng = rng.Offset(1, 0)
Next lngRow

Referring to Cells in a Named Range

A named range has a unique name, exposes events, and can be bound to data. Excel stores the names of defined ranges in the Names collection, which is a property of the Workbook object. Named ranges are a powerful tool in Excel that enable you to assign a meaningful name to a single cell or a range of cells. For example, you can assign the name "SalesTax" to cell C1 and then use the name "SalesTax" when you would normally use the cell C1, such as in the following code example.

Total = Range("A1") * Range("SalesTax")

Named ranges are discussed in more detail later in this article.

Selecting Ranges of Cells

There is no "ActiveRange" object with which to refer to the active range, but there are other ways of working with a range: the Select method lets you select a range of cells. After the range is selected, you can use the Selection object to control the actions on that selection of cells. In this example, the CurrentRegion property of the ActiveCell object is used to select the range, cell D1 in this instance, and copy the contents to the Clipboard. A new sheet is added after the last worksheet and the contents of the cell are copied to the new sheet. (The CurrentRegion property is discussed in more detail later.)

Range("D5").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Sample"
Sheets("Sample").Select
Range("D5").Activate
ActiveSheet.Paste

You can also perform other operations on a selected range of cells such as changing the font type to be bold, as in the following code example.

Selection.Font.Bold = True

You can use the End property of the Range object to instruct Excel which direction to extend the range. Enumerations are used describe the directions to extend the range. The Range object returned by the End method represents the cell at the end of the region that contains the source range. Using the End method is equivalent to pressing End+Up Arrow, End+Down Arrow, End+Left Arrow, or End+Right Arrow. This example selects the cell at the top of column B in the region that contains cell B4.

Range("B4").End(xlUp).Select

If you modify the enumeration to be xlToRight, then it will select the cell at the end of row 4 in the region that contains cell B4.

Range("B4").End(xlToRight).Select

The following code example extends the selection from cell B4 to the last cell in row four that contains data.

Range("B4", Range("B4").End(xlToRight)).Select

Combining Ranges and Checking for Overlapping Ranges

You can use the Union method of the Application object to combine multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object that is defined as the union of ranges A1:B2 and C3:D4, and then selects the newly defined range.

Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("Sheet1").Activate
Set rng1 = Range("A1:B2")
Set rng2 = Range("C3:D4")
Set myMultiRanges = Union(rng1, rng2)
myMultiRanges.Select

The Intersect method (Intersect(range1, range2)) returns a Range object that represents the rectangular intersection of two or more ranges. If the ranges do not intersect, the method returns the Nothing keyword. The following code example selects the intersection of two named ranges, range1 and range2, on Sheet1. If the ranges do not intersect, the example displays a message.

Worksheets("Sheet1").Activate 
Set interSect = Application.Intersect(Range("range1"), Range("range2")) 
If interSect Is Nothing Then 
   MsgBox "The ranges do not intersect." 
Else 
   interSect.Select 
End If

Named Ranges

As described previously, naming ranges enables you to provide a display name for a contiguous range of cells. Named ranges are the preferred method of referring to blocks of cells because using Range("Sales") is more informative than using Range("D1:D15"). A range name can contain letters, numbers, and underscores, but not spaces or special punctuation characters. Named ranges can be a maximum of 255 characters.

Defining a Named Range

To create a named range within the workbook, specify a name and the range of cells to which it should point. To do this, use the Add method of the Names collection.

ThisWorkbook.Names.Add Name:="Home_Sales", RefersTo:="=$A$1:$E$15", Visible:=True

Reserved Range Names

There are some range names that are reserved for use by Excel and are not available to name your own ranges. These include Consolidate_Area, Print_Titles, Auto_Open, Recorder, Auto_Close, Data_Form, Extract, Auto_Activate, Database, Auto_Deactivate, Criteria, Sheet_Title, and Print_Area.

However, even though you cannot use these names to define custom ranges for your own purpose, that does not mean you cannot use them. For example, by using the Print_Area name, you can specify what range of cells to print. In addition, the Print_Area range lets you set the range of cells by using the choice of A1 notation, R1C1 notation, or range names. The following sets the range of cells to print at A1:F15.

Sheet1.Names.Add "Print_Area", Sheet1.Range("A1:F15")

You can remove the range from the print area with this line of code, which uses the Delete method.

Sheet1.Names("Print_Area").Delete

Copying Ranges

The following sections describe ways of copying cells and ranges.

Copying Ranges of Cells

The following example copies cell A1 to cell B1 in the active worksheet. Be aware that you do not have to select the range before you copy it. If you do choose to select the range, the worksheet must be active. You can set the active worksheet by using the Activate method of the Worksheets collection.

Range("A1").Copy Range("B1")

To copy a range to a worksheet other than the active worksheet, just qualify the range reference.

Workbooks("File.xls").Sheets("Sheet1").Range("A1").Copy _
    Workbooks("File.xls").Sheets("Sheet2").Range("A1")

You can copy multiple cells the same way. In the following example, A1 will end up being the upper-left corner of the destination.

Workbooks("File.xls").Sheets("Sheet1").Range("B1:C5").Copy _
    Workbooks("File.xls").Sheets("Sheet2").Range("A1")

You can move a range instead of copying it by using the Cut method.

Range("A1").Cut Range("B1")

Copying Variable Size Ranges

In many instances, you do not know the exact size of the range that you want to copy or move. For example, you may be tracking weekly inventory and the number of rows fluctuates depending on your sales for the week. To handle this kind of situation, you use the CurrentRegion property. This property returns a Range object that references the cells that surround a particular cell.

Typically, the CurrentRegion property consists of a rectangular block of cells surrounded by (but not including) one or more blank rows or columns or by the edges of the worksheet. For example, if you had a table of data within cells D2 through E15 and the focus was in cell D2, the CurrentRegion property would return a Range object that represents cells D2 through E15.

The following code example copies the range of cells that surround the cell A1 in Sheet1 to the corresponding range in Sheet2.

Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

Copying Data between Ranges and Arrays

By using VBA arrays, you can access the individual cell values, moving data between the worksheet and VBA code. To do this, create a Variant data type and assign the range to that Variant type. Then you can access the individual cell values as you would any array element. The following code example copies the range B1:B10 into a Variant type and then displays the value of a single cell. Note that a range always returns a multidimensional array of at least two dimensions, one for rows and one for columns, and possibly more depending on the number of columns in the range.

Dim varA As Variant
Set varA = Range("B1:B10")
MsgBox (varA(3))

Looping Through Ranges

A common task for developers is looping through a range of values and performing some action on a cell or the cells in the range. The easiest way to do this is by using a form of the For...Next loop. This example loops through a named range of cells and, depending on their value, sets the font color by using the Cells property of the Range object, specifying the row and column values. Be aware that it uses nested For…Next loops, one for rows and one for columns.

Dim rngSales As Range
Dim lngRow As Long, lngColumn As Long
Set rngSales = Range("SalesData")
   
For lngRow = 1 To rngSales.Rows.Count
    For lngColumn = 1 To rngSales.Columns.Count
        If rngSales.Cells(lngRow, lngColumn).Value < 100 Then
            rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 3
        Else
            rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 1
        End If
        
    Next lngColumn
Next lngRow

Here is another example that shows how to loop through the rows in a range. In this code example, the rows are formatted as bold based on the first cell of the row exceeding a value of 1000.

Dim rngRow As Range
' Bold rows where total over 1000.
For Each rngRow In Range("Data").Rows
    If rngRow.Cells(1).Value > 1000 Then
        rngRow.Font.Bold = True
    Else
        rngRow.Font.Bold = False
    End If    
Next rngRow

Searching in Ranges

This section describes how to search for a value in a range of data by using the Find method. The following example searches for the string "Jun" in a range of values and if it is found, the code copies the row of data associated with that value and pastes it to another location; otherwise, it displays a message to the user. Be aware of the parameters associated with the Find method that specify what to look for, that we are matching against the whole, and that we are matching against the cell values.

Dim rng As Range
Set rng = Range("A1:A12").Find(What:="Jun", _
              LookAt:=xlWhole, LookIn:=xlValues)
If rng Is Nothing Then
    MsgBox "Data not found"
Else
    rng.Resize(1, 3).Copy Destination:=Range("G1")
End If

The Rice article (Developers Guide to the Excel 2007 Range Object) contains additional information about the Range object. It includes:

• How to use the End property to sum columns of values.

• How to see if a named range exists in a worksheet.

• How to delete the name of a named range.

• How to search through all worksheets in a workbook by using the Find method.

Length: 00:15:36

• Developers Guide to the Excel 2007 Range Object

• Getting Started with VBA Development in Office 2010

• Getting Started with VBA in Excel 2010

• Developers Guide to the Excel 2007 Application Object

About the Author

Peter Gruenbaum

  Peter Gruenbaum, started out as a physicist but became a software developer, working on technologies as diverse as Tablet PCs, Augmented Reality, computer-aided design and surgical simulation. He founded SDK Bridge LLC to bring together his love of technology and writing, where he writes and teaches about technology.