Ranges are a very important part of the Excel Object.

A Range is a contiguous groups of cells, on a worksheet. They can be as small as a singe cell, or as large as all the cells that will fit on a worksheet.

A Range must be on the same worksheet.


Ranges Collection

Define a Range

Define a Range Using a Cell Reference

Using Variables for Cell Reference

Using Variables for Cell Reference

Cells, Rows, Columns

Ranges, Unions and Intersections

Copy / Cut a Range

Insert a Range

Delete a Range

Select a Range


Ranges Collection

All Ranges are contained in a Ranges Collection. This collection contains all defined Ranges.

When creating a new Range, it is added to the Ranges Collection.

When a Range is deleted it is deleted from the Ranges Collection.


But in your code, a Range is always created and deleted within a worksheet object.

The only time you will actually reference the Range Collection is for its two methods, Count and Item.


Range Object

Define a Range

A "Range" uses A1 notation.


is the same as

This range contains the single cell at D5 :


You can define a range of more than one cell with the colon, :

The variable rng was assigned to this range. For the rest of the program we can reference it as rng


Define a Range Using a Cell Reference

With a cell reference, the row is listed first, then the column.

In this example, the cell at ROW 5, COLUMN 3 is C5.


In this example, the Range is defined with two cell references.

The cell at row 4, column 2 is B4.. The cell at row 6, column 5 is E6.

The comma, , is used to combine two ranges into one. This is the Union operator.


Using Variables for Cell Reference

An advantage of using the Cells syntax for Ranges is that you can use variables

In this example, the variable y starts at 1, which is the first row.

The range is defined as cell(y, 2) which is column 2. The row number will increase for each iteration.

The loop will end when it finds the first blank cell in column 2. It reads each row in column 2.

At the end of the loop, y will contain the row number of the first blank row.


Cells, Rows, Columns

Excel doesn't have a cell object. It doen't have a row or column object. These are referenced as range objects.

'This range contains 15 cells

'This range contains 5 rows

'This range contains 3 columns


Ranges, Unions and Intersections

Here is the Range notation - ("A2:D6"):

There are 20 highlighted cells.


A Union of ranges is written with a comma.


This combines the two ranges into one range.


An Intersection of ranges is written without a comma.


In this example only the overlapping cells are part of the range.


End property

This returns a Range that contains the end of the range to the top, bottom, left or right.

'The first non-empty cell up from B14, will be red.

'The last non-empty down from A10, will be red.

'The last non-empty cell to the right of B4 will be light blue.

'This is set up as a range, of "B6" to Range("B6").End(xlToRight)). This will cause the range of B6 to the first non-empty cell to the right of B6 to have a green font.


Copy / Cut a Range

Range.Copy [destination]

Range.Cut [destination]

You only need to define the upper left cell of the destination.

Cut works the same as Copy but it removes the range from its original position.


Copy the range in cells A1 to C6 and copy it to the Range starting at E2.

This will copy the range to E2 to G7.

The Copy and Cut methods have a destination parameter.

If you don't provide a destination, the range will be placed on the clipboard.


Insert a Range

Inserts a Range into the worksheet and shifts other cells away to make space.

rng.Insert (Shift)

The values for Shift are either xlShiftToRight or xlShiftDown

If you don't define a shift parameter, Excel will decide what to do. You don't want Excel quessing what to do with your data, so always specify a shift.


Delete a Range

Deletes a Range from the worksheet and shifts other cells fill the open space.

rng.Delete (Shift)

The values for Shift are either xlShiftToLeft or xlShiftUp

If you don't define a shift parameter, Excel will decide what to do. You don't want Excel quessing what to do with your data, so always specify a shift.


Select a Range

















































Copyright © 2006-2016, LQSystems,Inc. All rights reserved.


   Printer Friendly Page


Home