Developers Guide to the Excel 2007 Range Object
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)
Frank Rice, Microsoft Corporation
January 2008
Applies to: Microsoft Office Excel 2007
Contents
Overview
Inserting Values into a Range
Referring to Ranges
Selecting Ranges of Cells
Combining Ranges and Checking for Overlapping Ranges
More Information on Named Ranges
Copying Ranges
Looping Through Ranges
Searching in Ranges
Conclusion
Additional Resources
Overview
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 Range
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
Referring to Ranges
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.
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.
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
Combining Ranges and Checking for Overlapping Ranges
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
More Information on Named Ranges
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
Copying Ranges
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.
.gif "Note") Note |
|---|
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))
| Note |
|---|
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. |
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 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
Searching in Ranges
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
Conclusion
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.
Additional Resources
You can find more information on ranges at the following locations.