Michael Alexander - Excel 2019 Power Programming with VBA

The Rangeproperty applies to two types of objects: a Worksheetobject or a Rangeobject. Here, cell1and cell2refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). The following are a few examples of using the Rangeproperty.

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.3into cell A1 on Sheet1of the active workbook.

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

The Rangeproperty 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 in a range of 20 cells on the active sheet. If the active sheet isn't a worksheet, the statement causes an error message.

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

The next example produces 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 Valueproperty is omitted, so the default property (which is Valuefor a Rangeobject) is assumed. This example also uses the second syntax of the Rangeproperty. 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 3in cell C6:

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

Finally, if the range you're referencing is a noncontiguous range (a range where not all the cells are adjacent to each other), you can use commas to serve as a union operator. For example, the following statement enters the value 4in five cells that make up a noncontiguous range. Note that the commas are within the quote marks.

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

So far, all the examples have used the Rangeproperty on a Worksheetobject. As mentioned, you can also use the Rangeproperty on a Rangeobject. For example, the following line of code treats the Rangeobject as if it were the upper-left cell in the worksheet, and then it enters a value of 5in the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Rangeobject. Therefore, the statement that follows enters a value of 5into the cell directly to the right and one row below the active cell:

ActiveCell.Range("B2") = 5

Fortunately, you can access a cell relative to a range in a much clearer way—the Offsetproperty. We discuss this property after the next section.

Another way to reference a range is to use the Cellsproperty. You can use the Cellsproperty, like the Rangeproperty, on Worksheetobjects and Rangeobjects. Check the Help system, and you see that the Cellsproperty has three syntaxes.

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

Some examples demonstrate how to use the Cellsproperty. The first example enters the value 9in cell A1 on Sheet1. In this case, we're 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 7in cell D3 (that is, row 3, column 4) in the active worksheet:

ActiveSheet.Cells(3, 4) = 7

You can also use the Cellsproperty on a Rangeobject. When you do so, the Rangeobject returned by the Cellsproperty is relative to the upper-left cell of the referenced Range. Confusing? Probably. An example may help clear up any confusion. The following instruction enters the value 5in 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

The real advantage of this type of cell referencing will be apparent when you explore variables and looping (in Chapter 3, “VBA Programming Fundamentals”). In most cases, you don't use actual values for the arguments; rather, you use variables.

To enter a value of 5in 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 5in the cell in the second row and the first column.”

The second syntax of the Cellsproperty 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 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 cell is A2.

The next example enters the value 2into 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)

You can also use this syntax with a Rangeobject. In this case, the cell returned is relative to the Rangeobject referenced. For example, if the Rangeobject is A1:D10 (40 cells), the Cellsproperty can have an argument from 1to 40and can return one of the cells in the Rangeobject. In the following example, a value of 2000is entered in cell A2 because A2 is the 5th cell (counting from the top, to the right, and then down) in the referenced range:

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

In the preceding example, the argument for the Cellsproperty isn't limited to values between 1and 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 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 Cellsproperty simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data isn't a single cell. This example uses the ClearContentsmethod on the range returned by using the Cellsproperty on the active worksheet. The result is that the content of every cell on the worksheet is cleared.

ActiveSheet.Cells.ClearContents

If you need to get the contents of a cell, VBA provides several properties. The following are the most commonly used properties:

The Formula property returns the formula in a single cell, if the cell has one. If the cell doesn't contain a formula, it returns the value in the cell. The Formula property is a read/write property. Variations on this property include FormulaR1C1, FormulaLocal, and FormulaArray. (Consult the Help system for details.)