Excel® 2007 VBA Programmer's Reference

Chapter 4. Using Ranges

The Range object is probably the object you will utilize the most in your VBA code. A Range object can be a single cell, a rectangular block of cells, or the union of many rectangular blocks (a non-contiguous range). A Range object is contained within a Worksheet object.

The Excel object model does not support three-dimensional Range objects that span multiple worksheets — every cell in a single Range object must be on the same worksheet. If you want to process 3D ranges, you must process a Range object in each worksheet separately.

This chapter examines the most useful properties and methods of the Range object.

Activate and Select

The Activate and Select methods cause some confusion, and it is sometimes claimed that there is no difference between them. To understand the difference between them, you first need to understand the difference between the ActiveCell and Selection properties of the Application object. The screen in Figure 4-1 illustrates this.

Selection refers to B3:E10. ActiveCell refers to C5, the cell where data will be inserted if the user types something. ActiveCell only ever refers to a single cell, whereas Selection can refer to a single cell or a range of cells. The active cell is usually the top left-hand cell in the selection, but can be any cell in the selection, as shown in Figure 4-1. You can manually change the position of the active cell in a selection by pressing Tab, Enter, Shift+Tab, or Shift+Enter.

You can achieve the combination ...