Developer’s Guide to the Excel 2010 Range Object
Summary: Learn how to use the Range object in Microsoft Excel 2010 when you write macros in Visual Basic for Applications. The Range object represents a single cell, a row, a column, a selection of cells that contains one or more contiguous blocks of cells, or a 3-D range. This Visual How To shows how to access, name, select, copy, loop through, and find ranges.
Last modified: September 12, 2012
Applies to: Excel 2010 | Office 2010 | VBA
Published: April 2011
Provided by: Peter Gruenbaum | SDK Bridge
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:
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.
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.
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.
The following example inserts a single value into a range of cells.
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.
Again, by using the second form, the following statement puts the value "XYZ" in cells A1, A3, and A5 on Sheet2.
The syntax is similar for formulas. The following code example sets the formula for A1 equal to random value from 0 to 10.
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:
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.
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.
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.
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.
The following code example extends the selection from cell B4 to the last cell in row four that contains data.
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
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.
The following sections describe ways of copying cells and ranges.
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:
About the Author
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.