Range Structure

Section Goals

By the end of this section the reader should be able to:

Name Excel's four structural elements of ranges
Access cells by Index or Row and Column numbers
Access cells of a certain type within a range
Loop through cells in a range that may not have contiguous cells

Introduction

Ranges have four structural elements: Cells, Rows, Columns, and Areas.

A Cell is the smallest unit in a range.
A Row contains one or more cells next to each other in a single row
A Column contains one or more cells next to each other in a single column
An Area contains one or more cells next to each other that have all rows of the same length and all columns of the same length.

Cells

A cell is the smallest unit in a range. A range object can be as small as one cell or as large as all cells in the worksheet.

Access cells by index
We can address a worksheet's cells by index as in this example:

?ActiveSheet.Cells(1).Address
$A$1

While ActiveSheet.Cells(1) being $A$1 is obvious, do you know what address belongs to ActiveSheet.Cells(2)?

Access cells by row and column
We can also address a worksheet's cells by row and column number as in this example:

?ActiveSheet.Cells(1,2).Address
$B$1

Access cells by address
While cells are ranges, the range object reveals methods not available to cell objects. Accessing a cell by address must be done through the range object like so:

?ActiveSheet.Range("B1").Address
$B$1

Access cells by complex addresses
Ranges can use addresses to designate complex combinations of cells:

?ActiveSheet.Range("$A$1:$B$2,$A$3:$C$5").Address

Special Cells

Excel provides means to access cells in a region of a certain type, such as cells with formulas or blank cells. At right is a list of cell type constants. To select all cells in a worksheet contain formulas use:

Activesheet.cells.SpecialCells( _
xlCellTypeformulas).select

Areas

A single range can be complicated when the cells are not contiguous or the range has varied row width and/or column height.

Consider the range pictured below and to the right right. Each occupied cell has this formula

="R" & ROW() & "C" & COLUMN()

We created a range object by typing this into VBE's Immediate Window.

Set OccupiedRange = _
Activesheet.Cells.SpecialCells(xlCellTypeformulas)

OccupiedRange.Select

If we ask it how many areas it has, it replies 2.

?OccupiedRange.Areas.Count

OccupiedRange.Areas(1).Select

Area 1 is the selected cells. It has 4 cells, 2 rows, and 2 columns.

OccupiedRange.Areas(2).Select

Area 2 is the selected cells. It has 9 cells, 3 rows, and 3 columns.

If we ask OccupiedRange how many cells it has, it replies 13.

?OccupiedRange.Cells.Count

But if we ask OccupiedRange for the address of the 13th cell, it replies $A$7.

?OccupiedRange.Cells(13).Address

$A$7 is not one of the cells in OccupiedRange. This shows us is when, with VBA, we want to address individual cells in a range, where we have any doubt as to how many areas may be in the range, we must address cells within their area context like so:

For Each oArea in Selection.Areas

For Each oCell in oArea.Cells

We can also loop through these objects using their indexes, like so:

For n = 1 to Selection.Areas.Count

For x = 1 to Selection.Areas(n).Cells.Count

With Selection.Area(n).Cells(x)

End with