Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 8Working with Ranges

The Range object is probably the most frequently used object in VBA. Almost anything you do in a worksheet where VBA is concerned involves either a cell (or range of cells), or a reference of some kind to a range location that helps to direct whatever action your macro is undertaking. When you programmatically create a chart, modify a pivot table's source data, or insert picture files or comments, you are working with ranges.

Because ranges are so commonly referred to in code, this lesson introduces you to various syntaxes you will soon become familiar with, and in fact depend on, to refer to or manipulate Range objects. My approach with this lesson is to demonstrate basic code lines with pictures to show how ranges can be identified or selected.

As you'll hear over and over in VBA programming circles, you need not, and normally will not, actually select a range in order to work with it. You can refer to and manipulate (such as by editing or formatting) ranges of cells on other worksheets or other workbooks without leaving your active worksheet. The pictures in this lesson show the selection of ranges for visual confirmation of the code at work, but after this lesson, you will rarely see code that selects or activates an object.

Working with Contiguously Populated Ranges

The simplest ranges to deal with are those that have all cells filled with data or formulas, and no empty cells within that range. Figure 8.1 shows a typical-looking list of data ...