VBA: Methods for Referencing Excel Ranges

In Excel, in addition to selecting cells, you can also select groups of cells. A group of cells is called a range.

To select a range:

• Select the cell where you would like.
• Hold the left mouse button.
• Drag to the desired end cell.
• Release the mouse button.

Your selected cells will appear with a bold border around them.

A range name is composed as follow:

Uppermost Left Cell Address:Bottom Right Cell Address

Lets look at an example:

Like with cells, you can also select ranges using the name toolbox. Simply enter the address of range you would like to select in the box and press enter.

There are two main methods for selecting ranges in visual basic:

• Range Method:

Range("Range Address")

• Nesting Cells within Range Method:

Range(cells(row,column),cells(row,column))

Range("B10:F13").select

This would range "B10:F13"

We covered the referring to cells using the cells method in a previous module.

As you will recall the main advantage to using the cells method is a numeric variable can be accepted for either coordinate.

To use the cells method for selecting a range you must know reference the coordinates of the first and last cell in the range separated by a comma.

i.e.

Range(cells(10,2),cells(13,6))

This would also reference "B10:F13"

Practice selecting ranges using both methods. Be prepared in the next module we will look at executing some action on ranges. Click here when you are ready to proceed.