Export (0) Print
Expand All

Developers Guide to the Excel 2007 Range Object

Office 2007

Summary: Learn how to programmatically refer to and manipulate ranges of data in Microsoft Office Excel 2007. After you have specified the range, you can do things like define the range with a friendly name, use it in formulas, and validate the data contained in the range. (11 printed pages)

Much of the work that you do in Microsoft Office Excel 2007 relates to cells. Likewise, the majority of the work you do with the Excel object model involves working with ranges. A range can represent a single cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

The Range property returns a Range object. The Range property can have two forms.

expression.Range(cell1)
expression.Range(cell1, cell2)

In the first example, cell1 identifies the range of one cell; in the second, the two values represent the range of contiguous cells. In these examples, expression is a required element that must be either an Application object, a Range object, or a Worksheet object. 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 you can do with Excel ranges. However, these examples are just a subset of the things you can accomplish with ranges. For more information about the Range Object Model members, see Range Object Members.

Inserting values into a single cell in a range is straightforward.

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

Likewise, adding a value to a named cell can be done with the following.

ActiveSheet.Range("MyCell").Value = 1

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 in the first section, you can accomplish the same result with this statement.

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

Notice 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.

Again, when you are 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"

In the following example, you insert a formula into cell A1 on Sheet1.

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

This 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 

There are several ways to refer to a range.

  • By cell address

  • As an offset from another cell

  • By name

  • In the current selection

There are a couple of 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

The majority of the time when you are working with ranges, you refer to a range on the active worksheet. However, you can just as 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 refers to the cells B1 through B15.

Range("B1:B15").Select

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

Range("SalesTax").Select

This technique works regardless of which worksheet contains the named range is located.

Referring to Individual Cells in a Range

Referring to cells in a range is the same as referring to the cells in the entire workbook. For example, say that you designate the cells B4:E10 as the range. To refer to cell B4 in the range, you would use A1 in the Range object. To refer to cell D8 in the range, you would use C5 in the Range object.

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 specifying the number of columns and rows you are from a specific location, you can refer to the cell. Its syntax is as follows.

object.Offset(rowOffSet, columnOffset)

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 the starting location is cell E5.

The first 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 example calculates a moving average by using the Offset property.

Sub MovingAvg()
    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
    
End Sub

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.

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 anytime you would normally use the cell C1, such as in the following example.

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

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

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.

For example, you can copy a range of values to the Clipboard and insert them into a new worksheet.

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 

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.

You can also perform other operations on a selected range of cells such as displaying the selection in bold type.

Selection.Font.Bold = True

You can accomplish the same thing with this statement.

Range("C5:C20").Font.Bold = True

In addition to the CurrentRegion property, you can use the End method of the Range object to tell Excel in which direction to extend the range. Enumerations 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 

This example selects the cell at the end of row 4 in the region that contains cell B4.

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

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

Worksheets("Sheet1").Activate 
Range("B4", Range("B4").End(xlToRight)).Select 

The following sample uses the End method and its directional enumerations to sum columns of values. For example, assuming that you have columns of values that extend from cells B2 through G11, making cell B1 the active cell and running the macro places the totals at the top of each column of values.

Sub SumBelow()
    Dim rng As Range

    ' Sum cells below active cell & copy the totals across.

    With ActiveCell
        Set rng = Range(.Offset(1), .Offset(1).End(xlDown))
        .Formula = "=SUM(" & _
                   rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
        .Copy Destination:=Range(.Cells(1), .Offset(1).End(xlToRight).Offset(-1))
    End With

End Sub

You can use the Union method (Union(range1, range2, ...)) to combine multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object 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.

This example selects the intersection of two named ranges, rng1 and rng2, on Sheet1. If the ranges do not intersect, the example displays a message.

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

As you already saw, naming ranges allows you to provide a friendly name for a contiguous range of cells. 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. Named ranges are the preferred method of referring to blocks of cells. For example, using Range("Sales") is more informative than using Range("D1:D15").

Defining and Detecting a Named Range

Using the Add method of the Names collection, you can create a named range within the workbook by specifying a name and the range of cells it should point to.

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

Likewise, to see whether a named range exists in the worksheet, you can use the following example. This statement returns True if the named range is found; otherwise, it returns False.

Function NameExists(MyName As String) As Boolean
   On Error Resume Next
   NameExists = Len(ThisWorkbook.Names(MyName).Name) <> 0
End Function

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.

Even though you cannot use these names to define custom ranges for your own purposes, it does not mean that 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 the following statement.

Sheet1.Names("Print_Area").Delete

Deleting Named Ranges

The Delete method of the Names collection can be used to delete a named range.

ThisWorkbook.Names("Home_Sales ").Delete

The following sections describe ways of copying cells and ranges.

Copying Ranges of Cells

You can copy a range of cells from one location to another as with the following.

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

This operation assumes that the source and target locations are on the active worksheet. Notice that it is not necessary to select the range before copying 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.

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")

Of course, you can also move multiple-cell ranges by using the same syntax.

Range("C2:E10").Copy Range("D2")

In this example, the destination location represents the upper-left cell in the worksheet.

NoteNote

You can choose to move the range by substituting the Cut command for the Copy command.

Copying Variable Size Ranges

In many instances, you do not know the exact size of the range 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 type of situation, you use the CurrentRegion property. This property returns a Range object that conforms to the cells around 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 example copies the range of cells that surround the cell A1 in Sheet1to the corresponding range in Sheet2.

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

Copying Data between Ranges and Arrays

Accessing the individual cell values in a range is easy, as is moving data from the worksheet to Microsoft Visual Basic for Applications (VBA) and back. Just 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 example copies the range B1:B10 into a variant type and then displays the value of a single cell.

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

A range always returns a multidimensional array of at least two dimensions, one for row(s) and one for column(s), and possibly more depending on the number of columns in the range.

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 as in the following example. The example loops through a named range of cells and, depending on their value, sets the font color by using the Cell property of the Range object, specifying the row and column values.

Sub ColorCells()
    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

End Sub

A more efficient way to accomplish the same result is to use a single parameter range reference as in the following example.

Sub ColorCells2()
    Dim rngSales As Range
    Dim lngCell As Long

    Set rngSales = Range("SalesData")
    
    For lngCell = 1 To rngSales.Count
    
        If rngSales(lngCell).Value < 100 Then
            rngSales(lngCell).Font.ColorIndex = 3
        Else
            rngSales(lngCell).Font.ColorIndex = 1
        End If
        
    Next lCell

End Sub

The following example shows another way to loop through the range of cells where the cells are made bold based on cells exceeding a certain value.

Sub BoldRows()
    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
End Sub

Another common task for users is to search for a value in a range of data. The following example searches for the string "Jun" in a range of values and if it is found, copy the row of data associated with that value and paste it to another location; otherwise, display a message to the user. Notice the parameters associated with the Find method that specify what to look for and the scope of the search.

Sub FindIt()
    Dim rng As Range

    ' Find data and use Resize property to copy range.
    Set rng = Range("A1:A12").Find(What:="Jun", _
                                   LookAt:=xlWhole, LookIn:=xlValues)
    If rng Is Nothing Then
        MsgBox "Data not found"
        Exit Sub
    Else
        rng.Resize(1, 3).Copy Destination:=Range("G1")
    End If
    
End Sub

The following example lets you search through all of the worksheets in a workbook and reset the values not equal to zero in a set of noncontiguous ranges.

Sub ResetRangeValues()
    Dim wSheet As Worksheet
    Dim myRng As Range
    Dim allSheets As Sheets
    Dim cel As Range
    Set allSheets = Worksheets
    For Each wSheet In allSheets
        Set myRng = wSheet.Range("A1:A5, B6:B10, C1:C5, D4:D10")
        For Each cel In myRng
            If Not cel.HasFormula And cel.Value <> 0 Then
                cel.Value = 0
           End If
        Next cel
    Next wSht

End Sub

In this article, you learned how to refer to, and work with, ranges. The principle concept to take away from this is that, although you can refer to individual cells within a range, you can even call individual cells from within the Range object. After you have specified the range, you can define the range with a friendly name, use it in formulas, and validate the data contained in the range.

Show:
© 2014 Microsoft