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

Define a Range Using a Cell Reference

Using Variables for Cell Reference

Using Variables for Cell Reference

Ranges, Unions and Intersections

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

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")**

Dim wst As Worksheet

Dim rng As Range

Set wkb = Active.Workbook

Set wst = wkb.Worksheets(1)

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

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

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

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.

Set rng = wst.Range("A1:C6")

rng.Copy Range("E2") - - OR - - rng.Cut Range("E2")

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

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

rng.Delete Shift:=xlShiftUp OR xlShiftToLeft

Select a Range

range("A1:C3").select

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