Working with Range Objects

     

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 you want to work with.

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:B13 of the active worksheet (I'll discuss the Formula and DataSeries methods in more detail later):

 Range("B2").Value = #01/01/95# Range("B2:B13").DataSeries Type:=xlDate, 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 lower-right 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 Workbook 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.

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 

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 5     Cells(1, colNumber).Value = "Field" & colNumber Next colNumber 

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 8.4 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 (such as an Excel list).

Listing 8.4. 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 Integer     With rangeObject         totalRows = .Rows.Count         ' Total rows in the range         lastRow = .Rows(totalRows).Row  ' Last row number         .Rows(lastRow).Insert           ' Insert before last row     End With End Sub Sub InsertTest()     InsertRangeRow ThisWorkbook.Worksheets("Sheet1").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 . (You'll learn about the Insert method a bit later in the section titled "More Range Object Methods.")

To use InsertRangeRow , you need to pass a Range object to the procedure. For example, the InsertRange procedure shown at the end of Listing 8.4 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 

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

Listing 8.5. 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 & " " & string2 End 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 will set the font in the range B1:B10 without changing the selected cell:

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

tip

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" 

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 

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 11 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$C" 

More Range Object Properties

Some of the examples you've seen in the last few sections have used various Range object properties. Here's a review of a few more properties you're likely to use most often in your VBA code:

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 the right.

Range. Formula ” Returns or sets a formula for the specified Range .

Range. FormulaArray ” Returns or sets an array formula for the specified Range .

Range. NumberFormat ” Returns or sets the numeric format in the specified Range . Enter the format you want to use as a string, as shown in the following statement:

 Worksheets("Analysis").Range("Sales").NumberFormat = _     "$#,##0.00_);[Red]($#,##0.00)" 

Range. Row ” Returns the number of the first row in the specified Range .

Range. Value ” Returns or sets the value in the specified Range .

More Range Object Methods

Here's a look at a few more methods that should come in handy in your VBA procedures:

Range. Cut ” Cuts the specified Range to the Clipboard or to a new destination. The Cut method uses the following syntax:

 Range.Cut(  Destination  ) 

Range	The Range object to cut.
Destination	The cell or range where you want the cut range to be pasted.

For example, the following statement cuts the range A1:B3 and moves it to the range B4:C6:

 Range("A1:B3").Cut Destination:=Range("B4") 

Range. Copy ” Copies the specified Range to the Clipboard or to a new destination. Copying a range is similar to cutting a range. Here's the syntax for the Copy method:

 Range.Copy(  Destination  ) 

Range	The range to copy.
Destination	The cell or range where you want the copied range to be pasted.

Range. Clear ” Removes everything from the specified Range (contents, formats, and comments).

Range. ClearComments ” Removes the cell comments for the specified Range .

Range. ClearContents ” Removes the contents of the specified Range .

Range. ClearFormats ” Removes the formatting for the specified Range .

Range. DataSeries ” Creates a data series in the specified Range . The DataSeries method uses the following syntax:

 Range.DataSeries(  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.

Range. FillDown ” Uses the contents and formatting from the top row of the specified Range to fill down into the rest of the range.

Range. FillLeft ” Uses the contents and formatting from the rightmost column of the specified Range to fill left into the rest of the range.

Range. FillRight ” Uses the contents and formatting from the leftmost column of the specified Range to fill right into the rest of the range.

Range. FillUp ” Uses the contents and formatting from the bottom row of the specified Range to fill up into the rest of the range.

Range. Insert ” Inserts cells into the specified Range using the following syntax:

  Range  .Insert(  Shift  ) 

Range	The range into which you want to insert the cells.
Shift	The direction you want to shift the existing cells. Use either xlShiftToRight or xlShiftDown . If you omit this argument, Excel determines the direction based on the shape of Range .

Range. Resize ” Resizes the specified Range . 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.

For example, suppose you use the InsertRangeRow procedure from Listing 8.4 to insert a row into a named range. In most cases, you'll want to redefine the range name so that it includes the extra row you added. Listing 8.6 shows a procedure that calls InsertRangeRow and then uses the Resize method to adjust the named range.

Listing 8.6. 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").Select End 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.

The Absolute Minimum

This chapter showed you how to use VBA to manipulate Excel. You began by examining the Application object, where you learned how to access Excel's worksheet functions and saw a few useful Application object methods. Next you learned about Workbook objects, from specifying them to opening them to creating new ones. From there you tackled a few Workbook properties and methods. Worksheet objects came next, and you saw how to specify them and create them, as well as use their more common properties and methods. Finally, you learned about the most common Excel object, the Range , where you saw various methods for specifying ranges as well as numerous properties and methods that should prove useful.

Here are some chapters to check out for related information:

• For a general discussion of VBA objects, see Chapter 5, "Working with Objects."

• Some VBA functions perform the same tasks as some Excel worksheet functions. To find out which ones, see Appendix B, "VBA Functions."