Working with Range Objects

Mastering cell and range references is perhaps the most fundamental skill to learn when working with spreadsheets. After all, most worksheet chores involve cells, ranges, and range names. However, this skill takes on added importance when you're dealing with VBA procedures. When you're editing a worksheet directly, you can easily select cells and ranges with the mouse or the keyboard, or you can paste range names into formulas. In a procedure, though, you always have to describe-or even calculate-the range with which you want to work.

What you describe is the most common of all Excel VBA objects: the Range object. A Range object can be a single cell, a row or column, a selection of cells, or a 3D range. The following sections look at various techniques that return a Range object, as well as a number of Range object properties and methods.

Returning a Range Object

Much of your VBA code will concern itself with Range objects of one kind or another. Therefore, you need to be well versed in the various techniques that are available for returning range objects, whether they're single cells, rectangular ranges, or entire rows and columns. This section takes you through each of these techniques.

Using the Range Method

The Range method is the most straightforward way to identify a cell or range. It has two syntaxes. The first requires only a single argument:

Worksheet.Range(Name)

Worksheet
The Worksheet object to which the Range method applies. If you omit Worksheet, VBA assumes the method applies to the ActiveSheet object.

Name
A range reference or name entered as text.

For example, the following statements enter a date in cell B2 and then create a data series in the range B2:E10 of the active worksheet:

Range("B2").Value = #01/01/2008#Range("B2:B13").DataSeries Type:=xlChronological, Date:=xlMonth

The Range method also works with named ranges. For example, the following statement clears the contents of a range named Criteria in the Data worksheet:

Worksheets("Data").Range("Criteria").ClearContents

The second syntax for the Range method requires two arguments:

Worksheet.Range(Cell1, Cell2)

Worksheet
The Worksheet object to which the Range method applies. If you omit Worksheet, VBA assumes that the method applies to the ActiveSheet object.

Cell1, Cell2
The cells that define the upper-left corner (Cell1) and lowerright corner (Cell2) of the range. Each can be a cell address as text, a Range object consisting of a single cell, or an entire column or row.

The advantage of this syntax is that it separates the range corners into individual arguments. This lets you modify each corner under procedural control. For example, you could set up variables named upperLeft and lowerRight and then return Range objects of different sizes:

Range(upperLeft,lowerRight)

Using the Cells Method

The Cells method returns a single cell as a Range object. Here's the syntax:

Object.Cells(RowIndex, ColumnIndex)

Object
A Worksheet or Range object. If you omit Object, the method applies to the ActiveSheet object.

RowIndex
The row number of the cell. If Object is a worksheet, a RowIndex of 1 refers to row 1 on the sheet. If Object is a range, RowIndex 1 refers to the first row of the range.

ColumnIndex
The column of the cell. You can enter a letter as text or a number. If Object is a worksheet, a ColumnIndex of A or 1 refers to column A on the sheet. If Object is a range, ColumnIndex A or 1 refers to the first column of the range.

For example, the following procedure fragment loops five times and enters the values Field1 through Field5 in cells A1 through E1:

For colNumber = 1 To 5Cells(1, colNumber).Value = "Field" & colNumberNext colNumber

Tip: You also can refer to a cell by enclosing an A1-style reference in square brackets ([]). For example, the following statement clears the comments from cell C4 of the active worksheet:

ActiveSheet.[C4].ClearComments

Returning a Row

If you need to work with entire rows or columns, VBA has several methods and properties you can use. In each case, the object returned is a Range.

The most common way to refer to a row in VBA is to use the Rows method. This method uses the following syntax:

Object.Rows([Index])

Object
The Worksheet or Range object to which the method applies. If you omit Object, VBA uses the ActiveSheet object.

Index
The row number. If Object is a worksheet, an Index of 1 refers to row 1 on the sheet. If Object is a range, an Index of 1 refers to the first row of the range. If you omit Index, the method returns a collection of all the rows in Object.

For example, Listing shows a procedure named InsertRangeRow. This procedure inserts a new row before the last row of whatever range is passed as an argument (rangeObject). This would be a useful subroutine in programs that need to maintain ranges.

A Procedure That Uses the Rows Method to Insert a Row Before the Last Row of a Range

Sub InsertRangeRow(rangeObject As Range)Dim totalRows As Integer, lastRow As IntegerWith rangeObjecttotalRows = .Rows.Count ' Total rows in the rangelastRow = .Rows(totalRows).Row ' Last row number.Rows(lastRow).Insert ' Insert before last rowEnd WithEnd SubSub InsertTest()InsertRangeRow ThisWorkbook.Worksheets(1).Range("Test")End Sub

After declaring the variables, the first statement uses the Rows method without the Index argument to return a collection of all the rows in rangeObject and uses the Count property to get the total number of rangeObject rows:

totalRows = rangeObject.Rows.Count

The second statement uses the totalRows variable as an argument in the Rows method to return the last row of rangeObject, and then the Row property returns the row number:

lastRow = rangeObject.Rows(totalRows).Row

Finally, the last statement uses the Insert method to insert a row before lastRow. To use InsertRangeRow, you need to pass a Range object to the procedure. For example, the InsertRange procedure shown at the end of Listing show inserts a row into a range named Test.

You also can use the EntireRow property to return a row.The syntax Range.EntireRow returns the entire row or rows that contain the Range object.This is most often used to mimic the Shift+Spacebar shortcut key that selects the entire row that includes the active cell.To do this, you use the following statement:

ActiveCell.EntireRow.Select

Returning a Column

To return a column, use the Columns method. The syntax for this method is almost identical to the Rows method:

Object.Columns([Index])

Object
The Worksheet or Range object to which the method applies. If you omit Object, VBA uses the ActiveSheet object.

Index
The column number. If Object is a worksheet, an Index of A or 1 refers to column A on the sheet. If Object is a range, Index A or 1 refers to the first column of the range. If you omit Index, the method returns a collection of all the columns in Object.

For example, the following statement sets the width of column B on the active worksheet to 20:

Columns("B").ColumnWidth = 20

Note: The syntax Range.EntireColumn returns the entire column or columns that contain the specified Range object.

Using the Offset Method

When defining your Range objects, you often won't know the specific range address to use. For example, you might need to refer to the cell that's two rows down and one column to the right of the active cell. You could find out the address of the active cell and then calculate the address of the other cell, but VBA gives you an easier (and more flexible) way: the Offset method. Offset returns a Range object that is offset from a specified range by a certain number of rows and columns. Here is its syntax:

Range.Offset([RowOffset][, ColumnOffset])

Range
The original Range object.

RowOffset
The number of rows to offset Range. You can use a positive number (to move down), a negative number (to move up), or 0 (to use the same rows). If you omit RowOffset, VBA uses 0.

ColumnOffset
The number of columns to offset Range. Again, you can use a positive number (to move right), a negative number (to move left), or 0 (to use the same columns). If you omit ColumnOffset, VBA uses 0.

For example, the following statement formats the range B2:D6 as bold:

Range("A1:C5").Offset(1,1).Font.Bold = True

Listing shows a procedure called ConcatenateStrings that concatenates two text strings. This is handy, for instance, if you have a list with separate first and last name fields and you want to combine them.

A Procedure That Uses the Offset Method to Concatenate Two Text Strings

Sub ConcatenateStrings()Dim string1 As String, string2 As String'' Store the contents of the cell 2 to the left of the active cell'string1 = ActiveCell.Offset(0, -2)'' Store the contents of the cell 1 to the left of the active cell'string2 = ActiveCell.Offset(0, -1)'' Enter combined strings (separated by a space) into active cell'ActiveCell.Value = string1 & " " & string2End Sub

The procedure begins by declaring String1 and String2. The next statement stores in String1 the contents of the cell two columns to the left of the active cell by using the Offset method as follows:

String1 = ActiveCell.Offset(0, -2)

Similarly, the next statement stores in String2 the contents of the cell one column to the left of the active cell. Finally, the last statement combines String1 and String2 (with a space in between) and stores the new string in the active cell.

Selecting a Cell or Range

VBA lets you access objects directly without having to select them first. This means that your VBA procedures rarely have to select a range. For example, even if, say, cell A1 is currently selected, the following statement sets the font in the range B1:B10 without changing the selected cell:

Range("B1:B10").Font.Name = "Times New Roman"

However, there are times when you do need to select a range. For example, you might need to display a selected range to the user. To select a range, use the Select method:

Range.Select

Range
The Range object you want to select.

For example, the following statement selects the range A1:E10 in the Sales worksheet:

Worksheets("Sales").Range("A1:E10").Select

To return a Range object that represents the currently selected range, use the Selection property. For example, the following statement applies the Times New Roman font to the currently selected range:

Selection.Font.Name = "Times New Roman"

Selecting A1 on All Worksheets

When you open an Excel file that you've worked on before, the cells or ranges that were selected in each worksheet when the file was last saved remain selected upon opening. This is handy behavior because it often enables you to resume work where you left off previously. However, when you've completed work on an Excel file, you may prefer to remove all the selections. For example, you might run through each worksheet and select cell A1 so that you or anyone else opening the file can start "fresh."

Selecting all the A1 cells manually is fine if the workbook has only a few sheets, but it can be a pain in a workbook that contains many sheets. Listing 8.8 presents a macro that selects cell A1 in all of a workbook's sheets.

A Macro That Selects Cell A1 on All the Sheets in the Active Workbook

Sub SelectA1OnAllSheets()Dim ws As Worksheet'' Run through all the worksheets in the active workbook'For Each ws In ActiveWorkbook.Worksheets'' Activate the worksheet'ws.Activate'' Select cell A1'ws.[A1].SelectNext 'ws'' Activate the first worksheet'ActiveWorkbook.Worksheets(1).ActivateEnd Sub

The macro runs through all the worksheets in the active workbook. In each case, the worksheet is first activated (you must activate a sheet before you can select anything on it), and then the Select method is called to select cell A1. The macro finishes by activating the first worksheet.

Selecting the "Home Cell" on All Worksheets

Many worksheets have a "natural" starting point, which could be a model's first data entry cell or a cell that displays a key result. In such a case, rather than selecting cell A1 on all the worksheets, you might prefer to select each of these "home cells."

One way to do this is to add a uniform comment to each home cell. For example, you could add the comment Home Cell. Having done that, you can then use the macro in Listing show to select all these home cells.

A Macro That Selects the "Home Cell" on All the Sheets in the Active Workbook

Sub SelectHomeCells()Dim ws As WorksheetDim c As CommentDim r As Range'' Run through all the worksheets in the active workbook'For Each ws In ActiveWorkbook.Worksheets'' Activate the worksheet'ws.Activate'' Run through the comments'For Each c In ws.Comments'' Look for the "Home Cell" comment'If InStr(c.Text, "Home Cell") <> 0 Then'' Store the cell as a Range'Set r = c.Parent'' Select the cell'r.SelectEnd IfNext 'cNext 'ws'' Activate the first worksheet'ActiveWorkbook.Worksheets(1).ActivateEnd Sub

The SelectHomeCells procedure is similar to the SelectA1OnAllSheets procedure from previous Listing. That is, the main loop runs through all the sheets in the active workbook and activates each worksheet in turn. In this case, however, another loop runs through each worksheet's Comments collection. The Text property of each Comment object is checked to see whether it includes the phrase Home Cell. If so, the cell containing the comment is stored in the r variable (using the Comment object's Parent property) and then the cell is selected.

Selecting the Named Range That Contains the Active Cell

It's often handy to be able to select the name range that contains the current cell (for example, to change the range formatting). If you know the name of the range, you need only select it from the Name box. However, in a large model or a workbook that you're not familiar with, it may not be obvious which name to choose. Listing 8.10 shows a function and procedure that will handle this chore for you.

A Function and Procedure That Determine and Select the Named Range Containing the Active Cell

Function GetRangeName(r As Range) As StringDim n As NameDim rtr As RangeDim ir As Range'' Run through all the range names in the active workbook'For Each n In ActiveWorkbook.Names'' Get the name's range'Set rtr = n.RefersToRange'' See whether the named range and the active cell's range intersect'Set ir = Application.Intersect(r, rtr)If Not ir Is Nothing Then'' If they intersect, then the active cell is part of a' named range, so get the name and exit the function'GetRangeName = n.NameExit FunctionEnd IfNext 'n'' If we get this far, the active cell is not part of a named range,' so return the null string'GetRangeName = ""End FunctionSub SelectCurrentNamedRange()Dim r As RangeDim strName As String'' Store the active cell'Set r = ActiveCell'' Get the name of the range that contains the cell, if any'strName = GetRangeName(r)If strName <> "" Then'' If the cell is part of a named range, select the range'Range(strName).SelectEnd IfEnd Sub

The heart of Listing above is the GetRangeName function, which takes a range as an argument. The purpose of this function is to see whether the passed range-r-is part of a named range and if so, to return the name of that range. The function's main loop runs through each item in the active workbook's Names collection. For each name, the RefersToRange property returns the associated range, which the function stores in the rtr variable. The function then uses the Intersect method to see whether the ranges r and rtr intersect. If they do, it means that r is part of the named range (because, in this case, r is just a single cell), so GetRangeName returns the range name. If no intersection is found for any name, the function returns the null string (""), instead.

The SelectCurrentNamedRange procedure makes use of the GetRangeName function. The procedure stores the active cell in the r variable and then passes that variable to the GetRangeName function. If the return value is not the null string, the procedure selects the returned range name.

Defining a Range Name

In Excel VBA, range names are Name objects. To define them, you use the Add method for the Names collection (which is usually the collection of defined names in a workbook). Here is an abbreviated syntax for the Names collection's Add method (this method has eleven arguments; see the VBA Reference in the Help system):

Names.Add(Text, RefersTo)

Text
The text you want to use as the range name.

RefersTo
The item to which you want the name to refer. You can enter a constant, a formula as text (such as "=Sales-Expenses"), or a worksheet reference (such as "Sales!A1:C6").

For example, the following statement adds the range name SalesRange to the Names collection of the active workbook:

ActiveWorkbook.Names.Add _Text:="SalesRange", _RefersTo:="=Sales!$A$1$C$6"

Inserting Data into a Range

If your VBA procedure needs to add data to a range, VBA offers several properties that can do this. (Note that all these properties also return the current data that resides in a range.) If you just want to add a simple value such as a number, string, date, or time to a range, use the Range object's Value property. For example, the following statement inserts the current date and time into cell A1 on the active worksheet:

ActiveSheet.Range("A1").Value = Now

Similarly, the following statement fills the range A2:D20 with zeroes:

ActiveSheet.Range("A2:D20).Value = 0

If you want to add a formula to a range, use the Range object's Formula property. For example, the following statement adds a formula to cell E15:

ActiveWorkbook.Worksheets("Budget").Range("E15").Formula = "=E5 - E14"

If you need to enter an array formula into a cell, use the Range object's FormulaArray property. Note that you don't include the braces, as shown in the following example:

ActiveCell.FormulaArray = "=SUM(IF(A2:C5 > 0, 1, 0))"

Excel also enables you to enter a data series into a range by using the Range object's DataSeries method. The DataSeries method uses the following syntax:

Range.DataSeries(Range[, Rowcol][, Type][, Date][, Step][, Stop][, Trend])

Range
The range to use for the data series.

Rowcol
Use xlRows to enter the data in rows, or xlColumns to enter the data in columns. If you omit Rowcol, Excel uses the size and shape of Range.

Type
The type of series. Enter xlLinear (the default), xlGrowth, xlChronological, or xlAutoFill.

Date
The type of date series, if you used xlChronological for the Type argument. Your choices are xlDay (the default), xlWeekday, xlMonth, or xlYear.

Step
The step value for the series (the default value is 1).

Stop
The stop value for the series. If you omit Stop, Excel fills the range.

Trend
Use True to create a linear or growth trend series. Use False (the default) to create a standard series.

For example, the following statements insert a date into cell B2 and then create a data series in the range B2:B13:

ActiveSheet.Range("B2").Value = #01/01/2008#ActiveSheet.Range("B2:B13").DataSeries Type:=xlChronological, Date:=xlMonth

Returning Data About a Range

Here's a list of some Range object properties that return data about a range:

• Range.Address-Returns the address, as text, of the specified Range.
• Range.Column-Returns the number of the first column in the specified Range.
• Range.Count-Returns the number of cells in the specified Range.
• Range.CurrentRegion-Returns a Range object that represents the entire region in which the specified Range resides. A range's "region" is the area surrounding the range that is bounded by at least one empty row above and below, and at least one empty column to the left and right.
• Range.Row-Returns the number of the first row in the specified Range.

Resizing a Range

When you need to resize a range, use the Range object's Resize method. Here's the syntax for this method:

Range.Resize(RowSize, ColSize)

Range
The range to resize.

RowSize
The number of rows in the new range.

ColSize
The number of columns in the new range.

Listing below shows a procedure that calls InsertRangeRow and then uses the Resize method to adjust the named range.

A Procedure That Uses Resize to Adjust a Named Range

Sub InsertAndRedefineName()With ThisWorkbook.Worksheets("Test Data")InsertRangeRow .Range("Test")With .Range("Test")Names.Add _Name:="Test", _RefersTo:=.Resize(.Rows.Count + 1)End With.Range("Test").SelectEnd Sub

In the Names.Add method, the new range is given by the expression .Resize(.Rows.Count + 1). Here, the Resize method returns a range that has one more row than the Test range.