Programming Excel with VBA and .NET

Work with Ranges

When you want to do just about anything in code with a cell or group of cells in a worksheet, you do it using a Range object. It is the most frequently used object in Excel programming.

It can also be confusing. You can return a Range object in many ways in your code, and it can represent both individual cells and groups of cells, depending on the circumstances. Compounding the confusion, the Excel programming reference topics do not document Range as an object, even though Range objects are referred to frequently throughout the reference documentation, and you often declare variables of type Range. Once you become familiar with Range objects, however, they are not difficult to use.

The most common way to return a Range object is using the Range property, which lets you specify a single cell or a range of cells. The following code returns the value of cell A9 on the currently active worksheet:

ActiveSheet.Range("A9")

The following code selects all the cells in the range A1:A9:

ActiveSheet.Range("A1:A9").Select

Another common way to return a Range object is to use the Cells property to return an individual cell based on its row and column position in a worksheet. For example, the following code sets the value of cell F4 (the cell in the fourth row and sixth column) to 12:

ActiveSheet.Cells(4, 6)=12

The advantage of using the Cells property to return a range is that you can use variables to represent the row or column values. For example, the following code uses the ...