VBA Programming Series...31

Working with Range Objects

A Range object represents a range contained in a Worksheet object. Range objects, like all other objects, have properties (which you can examine and change) and methods (which perform actions on the object).

A Range object can be as small as a single cell (for example, B4) or as large as every one of the 17,179,869,184 cells in a worksheet (A1:XFD1048576).

When you refer to a Range object, the address is always surrounded by double quotes, like this:

If the range consists of one cell, you still need the quotes:

Range(“K9”)

If the range happens to have a name (created by using Formulas➪Defined Names➪Define Name), you can use an expression like this:

Range(“PriceList”)

Unless you tell Excel otherwise by qualifying the range reference, it assumes that you’re referring to a range on the active worksheet. If anything other than a worksheet is active (such as a chart sheet), the range reference fails, and your macro displays an error message.

As shown in the following example, you can refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook:

Worksheets(“Sheet1”).Range(“A1:C5”)

If you need to refer to a range in a different workbook (that is, any workbook other than the active workbook), you can use a statement like this:

Workbooks(“Budget.xlsx”).Worksheets(“Sheet1”). _

Range(“A1:C5”)

A Range object can consist of one or more entire rows or columns. You can refer to an entire row (in this case, row 3) by using syntax like this:

Range(“3:3”)

You can refer to an entire column (column 4 in this example) like this:

Range(“D:D”)

In Excel, you select noncontiguous ranges by holding down the Ctrl key while selecting various ranges with your mouse. Figure shows a noncontiguous range selection. You shouldn’t be surprised that VBA also lets you work with noncontiguous ranges. The following expression refers to a two-area noncon-tiguous range. Notice that a comma separates the two areas.

Range(“A1:B8,D9:G16”)

Be aware that some methods and properties cause havoc with noncontiguous ranges. You may have to process each area separately by using a loop.