Working with Range Objects

Much of the work that you will do in VBA involves cells and ranges in worksheets. The earlier discussion on relative versus absolute macro recording (see "Relative or absolute?") exposes you to working with cells in VBA, but you need to know a lot more.

A Range object is contained in a Worksheet object and consists of a single cell or range of cells on a single worksheet. In the sections that follow, I discuss three ways of referring to Range objects in your VBA code:

• The Range property of a Worksheet or Range class object

• The Cells property of a Worksheet object

• The Offset property of a Range object

The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes:

 object.Range(cell1) object.Range(cell1, cell2) 

Working with Merged Cells

Working with merged cells can be tricky. If a range contains merged cells, you may need to take some special action with the macros. For example, if cells A1:D1 are merged, the statement that follows selects columns A through D (not just column B, as you might expect):

 Columns("B:B").Select 

I don't know if this unexpected behavior is intentional or if it's a bug. However, it can cause your macro to behave in a manner that you didn't expect. Merged cells also cause problems with sorting.

To determine if a particular range contains any merged cells, you can use the following VBA function. The function returns True if any cell in the argument range is a merged cell (refer to Chapter 10 for more information about Function procedures).

 Function ContainsMergedCells(rng As Range)     Dim cell As Range     ContainsMergedCells = False     For Each cell In rng         If cell.MergeCells Then             ContainsMergedCells = True             Exit Function         End If     Next cell End Function 

To refer to merged cells, you can reference the entire merged range or just the upperleft cell within the merged range. For example, if a worksheet contains four cells merged into one (A1, B1, A2, and B1), reference the merged cells using either of the following expressions:

 Range("A1:B2") Range("A1") 

If you attempt to assign a value to a cell in a merged range that's not the upper-left cell, VBA ignores the instruction and does not generate an error. For example, the following statement has no effect if A1:B2 is merged:

 Range("B2").Value = 43 

Some operations cause Excel to display a confirmation message. For example, if A1:B2 is merged, the following statement generates a message: This operation will cause some merged cells to unmerge. Do you wish to continue?

 Range("B2").Delete 

Bottom line? Be careful with merged cells. Clearly, this feature was not very well thought out before it was implemented.

 

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). Following are a few examples of using the Range property.

You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell A1 on Sheet1 of the active workbook:

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

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input , you can use the following statement to enter a value into that named cell:

 Worksheets("Sheet1").Range("Input").Value = 100 

The example that follows enters the same value into a range of 20 cells on the active sheet. If the active sheet is not a worksheet, this causes an error message:

 ActiveSheet.Range("A1:B10").Value = 2 

The next example produces exactly the same result as the preceding example:

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

The sheet reference is omitted, however, so the active sheet is assumed. Also, the value property is omitted, so the default property (which is Value for a Range object) is assumed. This example also uses the second syntax of the Range property. With this syntax, the first argument is the cell at the top left of the range, and the second argument is the cell at the lower right of the range.

The following example uses the Excel range intersection operator (a space) to return the intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement enters 3 into cell C6:

 Range("C1:C10 A6:E6") = 3 

And finally, this next example enters the value 4 into five cells: that is, a noncontiguous range. The comma serves as the union operator:

 Range("A1,A3,A5,A7,A9") = 4 

So far, all the examples have used the Range property on a Worksheet object. As I mentioned, you can also use the Range property on a Range object. This can be rather confusing, but bear with me.

Following is an example of using the Range property on a Range object. (In this case, the Range object is the active cell.) This example treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 into the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Range object. Therefore, the statement that follows enters a value of 5 into the cell directly to the right and one row below the active cell:

 ActiveCell.Range("B2") = 5 

I said this is confusing. Fortunately, there is a much clearer way to access a cell relative to a range: the Offset property. I discuss this property after the next section.

The Cells property

Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. Check the Help system, and you see that the Cells property has three syntaxes:

   object   .Cells(   rowIndex   ,   columnIndex   )   object   .Cells(   rowIndex   )   object   .Cells 

I'll give you some examples that demonstrate how to use the Cells property. The first example enters the value 9 into cell A1 on Sheet1 . In this case, I'm using the first syntax, which accepts the index number of the row (from 1 to 1048576) and the index number of the column (from 1 to 16384):

 Worksheets("Sheet1").Cells(1, 1) = 9 

Here's an example that enters the value 7 into cell D3 (that is, row 3, column 4) in the active worksheet:

 ActiveSheet.Cells(3, 4) = 7 

You can also use the Cells property on a Range object. When you do so, the Range object returned by the Cells property is relative to the upper-left cell of the referenced Range . Confusing? Probably. An example might help clear this up. The following instruction enters the value 5 into the active cell. Remember, in this case, the active cell is treated as if it were cell A1 in the worksheet:

 ActiveCell.Cells(1, 1) = 5 

Note  

The real advantage of this type of cell referencing will be apparent when I discuss variables and looping (see Chapter 8). In most cases, you don't use actual values for the arguments; rather, you use variables .

To enter a value of 5 into the cell directly below the active cell, you can use the following instruction:

 ActiveCell.Cells(2, 1) = 5 

Think of the preceding example as though it said this: "Start with the active cell and consider this cell as cell A1. Place 5 in the cell in the second row and the first column."

The second syntax of the Cells method uses a single argument that can range from 1 to 17,179,869,184. This number is equal to the number of cells in an Excel 2007 worksheet. The cells are numbered starting from A1 and continuing right and then down to the next row. The 16,384th cell is XFD1; the 16,385th is A2.

The next example enters the value 2 into cell SZ1 (which is the 520th cell in the worksheet) of the active worksheet:

 ActiveSheet.Cells(520) = 2 

To display the value in the last cell in a worksheet (XFD1048576), use this statement:

 MsgBox ActiveSheet.Cells(17179869184) 

This syntax can also be used with a Range object. In this case, the cell returned is relative to the Range object referenced. For example, if the Range object is A1:D10 (40 cells), the Cells property can have an argument from 1 to 40 and can return one of the cells in the Range object. In the following example, a value of 2000 is entered into cell A2 because A2 is the fifth cell (counting from the top, to the right, and then down) in the referenced range:

 Range("A1:D10").Cells(5) = 2000 

Note  

In the preceding example, the argument for the Cells property is not limited to values between 1 and 40 . If the argument exceeds the number of cells in the range, the counting continues as if the range were taller than it actually is. Therefore, a statement like the preceding one could change the value in a cell that's outside of the range A1:D10. The statement that follows, for example, changes the value in cell A11:

 Range("A1:D10").Cells(41)=2000 

The third syntax for the Cells property simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data is not a single cell. This example uses the ClearContents method on the range returned by using the Cells property on the active worksheet. The result is that the content of every cell on the worksheet is cleared:

 ActiveSheet.Cells.ClearContents 

The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:

 object.Offset(rowOffset, columnOffset) 

The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or zero. The example that follows enters a value of 12 into the cell directly below the active cell:

 ActiveCell.Offset(1,0).Value = 12 

The next example enters a value of 15 into the cell directly above the active cell:

 ActiveCell.Offset(-1,0).Value = 15 

If the active cell is in row 1, the Offset property in the preceding example generates an error because it cannot return a Range object that doesn't exist.

The Offset property is quite useful, especially when you use variables within looping procedures. I discuss these topics in the next chapter.

When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, I used the macro recorder to generate the following code. I started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.

 Sub Macro1()     ActiveCell.FormulaR1C1 = "1"     ActiveCell.Offset(1, 0).Range("A1").Select     ActiveCell.FormulaR1C1 = "2"     ActiveCell.Offset(1, 0).Range("A1").Select     ActiveCell.FormulaR1C1 = "3"     ActiveCell.Offset(-2, 0).Range("A1").Select End Sub 

Notice that the macro recorder uses the FormulaR1C1 property. Normally, you want to use the Value property to enter a value into a cell. However, using FormulaR1C1 or even Formula produces the same result.

Also notice that the generated code references cell A1 - a cell that was even involved in the macro. This notation is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range("A1") , and the macro still works perfectly :

 Sub Modified Macro1()     ActiveCell.FormulaR1C1 = "1"     ActiveCell.Offset(1, 0).Select     ActiveCell.FormulaR1C1 = "2"     ActiveCell.Offset(1, 0).Select     ActiveCell.FormulaR1C1 = "3"     ActiveCell.Offset(-2, 0).Select End Sub 

In fact, here's a much more efficient version of the macro (which I wrote myself ) that doesn't do any selecting:

 Sub Macro1()     ActiveCell = 1     ActiveCell.Offset(1, 0) = 2     ActiveCell.Offset(2, 0) = 3 End Sub