VBA Macro Excel Tutorial: Range Objects

Range object is the heart of VBA Macro Excel programming, because much of the work you do in VBA Macro Excel involves ranges and cells in worksheets. A range object consists of a single cell or a range of cells on a single worksheet. There are three ways of referring to range objects:

• Range property
• Cells property
• Offset property

The Range property
The Range property has two syntaxes:

object.Range(cell)
object.Range(cell1, cell2)

Following example puts words "VBA Macro Excel" into a range A1:E1 on active sheet of the active workbook:

ActiveSheet.Range("A1:E1").Value = "VBA Macro Excel"

The next example produce the same result as the preceding example:

ActiveSheet.Range("A1", "E1").Value = "VBA Macro Excel"


The Cells property
The Cells property has three syntaxes:

object.Cells(rowIndex, columnIndex)
object.Cells(rowIndex)
object.Cells

This following example puts the value 5 into cell B5 on Sheet1 of the active workbook:

Sheets("Sheet1").Cells(5, 2).Value = 5

The second syntax of the Cells method uses a single argument that can range from 1 to 16,777,216 (256 columns x 65,536 rows). The cells are numbered from A1 continuing right then down to the next row.

For example, to enters value 2 into cells B2 of active sheet:

ActiveSheet.Cells(258).Value = 2

The third syntax for the Cells property returns all cells on the referenced worksheet. The following example will erase all cells value and its format:

ActiveSheet.Cells.Clear


The Offset property
The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The Offset property syntax is follow:

object.Offset(rowOffset, columnOffset)

The following example puts words "VBA Macro Excel" into the cell above the active cell:

ActiveCell.Offset(-1, 0).Value = "VBA Macro Excel"