Excel Ranges

by Linda Quinn

Close Window Print Page

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

Cells, Rows, Columns

Ranges, Unions and Intersections

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.

wst.Range("A1:A10").Cells

is the same as

wst.Range("A1:A10")

This range contains the single cell at D5 :

Dim wkb As Workbook
Dim wst As Worksheet
Dim rng As Range

Set wkb = Active.Workbook
Set wst = wkb.Worksheets(1)
Set rng = wst.Range("D5")

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

Set rng = wst.Range("A2:D6")

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.

Set rng = wst.Cell(5,3)

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.

Set rng = wst.Range(Cells(4,2), Cells(6,5))

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

Set rng = wst.Cell(y,2)
y = 1
Do while wst.cells(y,2) <> ""
    If wst.cells(y, 2) <> "" Then
        y = y + 1
    End If
Loop

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.

Set rng = Range("A1:C5").Cells

'This range contains 15 cells

Set rng = Range("A1:C5").Rows

'This range contains 5 rows

Set rng = Range("A1:C5").Columns

'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.

Set rng1 = wst.Range("B3:G5, C2:D9")
rng1.Interior.ColorIndex = 15

This combines the two ranges into one range.

An Intersection of ranges is written without a comma.

Set rng2 = wst.Range("B3:G5 C2:D9")
rng2.Interior.ColorIndex = 15

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.

wst.Range("B14").End(xlUp).Interior.ColorIndex = 3

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

wst.Range("A10").End(xlDown).Interior.ColorIndex = 3

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

wst.Range("B4").End(xlToRight).Interior.ColorIndex = 8

'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.

wst.Range("B6", Range("B6").End(xlToRight)).Font.Color = vbGreen

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.

Set rng = wst.Range("A1:C6")
rng.Copy Range("E2") - - OR - - rng.Cut Range("E2")

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.

Set rng = wst.Range("A12:G12")
rng.Insert Shift:=xlShiftDown OR xlShiftToRight

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.

Set rng = wst.Range("A12:G12")
rng.Delete Shift:=xlShiftUp OR xlShiftToLeft

Select a Range

range("A1:C3").select