Using VBA Range Object in Microsoft Excel

When learning Excel VBA, a critical skill is to be able to refer to a range. You will need to read, write and perform many other actions on sheet ranges. There are a few ways to refer to ranges in Excel VBA and the Range object is one of the most used.

In fact, if you have recorded a macro in Excel before and clicked on a cell during the recording, the Range object would have been used in the generated VBA code. The Excel VBA Range object is very important to know and it can do much more than simply select a range (which is what you generally see in recorded code).

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Start free trial

What is the Excel VBA Range object?

The VBA Range object is an object in the Excel VBA object model. By using the Range object, you can reference a range on a worksheet and return information about its status (properties), or perform actions (methods) on it.

The syntax of the VBA Range object is:

Range(Cell1, [Cell2])

The Cell2 argument is optional and can be used to reference the last cell in a range, or a second non-contiguous range. Let’s see how this Range object can be used.

Using Range in Excel VBA

The VBA Range object can be used to reference a single cell range, multiple cell range, entire columns or rows, and also non-contiguous cell ranges.

The following examples demonstrate each of these techniques. The Select method is used in some examples to better visualise the range that was used. To reference a single cell range explicitly, enter the cell address within double quotations.

Range("B2")

To refer to a range of cells, enter the range within double quotations. The range can be entered just like in a typical range reference with the first and last cell separated by a colon (:)

Range("A1:D8")

An alternative way to enter a multiple-cell range using the VBA Range object is to use both the Cell1 and Cell2 arguments. Each range is enclosed in their own double quotations.

To be clear, the following code refers to all cells between cell A1 and cell D8.

Range("A1", "C8")

If you use both the Cell1 and Cell2 arguments, but enclose them in the same single use of double quotations, this refers to the two cells distinctly.

Range("A1, C8")

You can also use this approach for multiple cell ranges.

Range("A1:A8, C1:C8")

The Excel VBA Range object can also be used to reference entire rows and columns.

Range("B:B")
Range("3:3")

As the Range object accepts the reference entered within double quotations, you can also concatenate values to form a range from different parts. For example, you may have a variable that stores the total number of rows in a range. This value can be appended to a partly entered range.

Range("A1:B" & TotalRows)

The End(XLDown) property is used in this example to return the row number of the range at the bottom of a column of contiguous used cells.

You can also reference named ranges within the Range object. For example, if you had a named range of "Sales", the following code could be used to select it.

Range("Sales").Select

Useful Excel VBA Range object properties

There are many useful properties (information about the range) and methods (actions on the range) of the range object.

As an Excel user you will be familiar with many properties and methods already.

Properties include the range Address, Font, Borders, and Locked status.  While methods include Select, Copy, and AddComment.

To cover all these Excel VBA Range object properties and methods is beyond the scope of this tutorial. You will discover them as you use Range in your VBA task.

Let’s look at a very useful property of the Range object that helps us to reference ranges dynamically - the CurrentRegion property.

The Range.CurrentRegion property

The CurrentRegion property of the Range object enables you to easily select the entire range of contiguous cells surrounding a given cell.

The current region stops at the first blank row and column.

In the following example, the current region from range A2 is selected.

Range("A2").CurrentRegion.Select

Using the CurrentRegion property you can easily find the last row or last column of a contiguous range of cells. This is a very common requirement in VBA.

Range("A2").CurrentRegion.Rows.Count
Range("A2").CurrentRegion.Columns.Count

If the range you require is not contiguous, you may consider the UsedRange property. UsedRange belongs to the Worksheet object, and not Range.

Ranges within a range

An aspect that can be very confusing, but at times useful, is to reference a range within a range.

For example, say you want to reference the second column in a specified range. The following code selects range B1:B6, but if the range was D1:E6, it would have selected range E1:E6.

Range("A1:B6").Columns(2).Select

Or, maybe you want to reference the first cell of the selected range.

Selection.Range("A1")

This code uses the Range object within the Selection object, which is itself a range.

In this example, the selected range is B3:C8, and because the Range object is within the Selection object, range A1 refers to cell B3 on the worksheet, and not A1.

The Worksheet.Range object

So far, the examples have used the Range object on the active sheet only. However, it is likely that you will need to reference a range on another worksheet.

In the VBA object model, the Range object is contained within the Worksheet object. So, to qualify that you need the range on a specific worksheet, you reference the Worksheet object first, followed by a period (.) and then the Range object.

In the following code, range A1:B6 on a worksheet named “Data” has been copied to range B2 on a worksheet named “Archive”.

Worksheets("Data").Range("A1").CurrentRegion.Copy _

    Worksheets("Archive").Range("B2")

In this example, the CurrentRegion property that we covered previously was utilised. The “ _” was used on the end of the first line to continue the same VBA statement on the second line.

Excel VBA Range variable

The Excel VBA Range variable is an object variable that can be used to simplify a reference to a range, especially when used repetitively in code.

It can be used to prevent having to repeat the explicit address of a range. In the following code, a Range variable named “rng” has been declared and then set as cell A2 of the “Data” worksheet.

The rng variable is then referenced five times in the code. It is easier in this instance to reference rng versus the complete Worksheet.Range reference.

Sub UsingRange()

Dim rng As Range

Dim TotalRows As Long

Set rng = Worksheets("Data").Range("A2")

TotalRows = rng.CurrentRegion.Rows.Count

Do While rng.Row <= TotalRows

    If rng.Value >= 10 Then

        rng.Interior.ColorIndex = 37

    End If

    Set rng = rng.Offset(1, 0)

Loop

End Sub

This example is a little extreme, but it demonstrates the technique of setting a VBA range variable and using it within a task.

An Excel VBA Range variable can also be used when you do not know, or need to know, the address of a range. A typical example of this is when using For Each Next loops to repeat an action for each cell of a given range.

In the following example, the rng range variable is used to loop through each cell of the selected range and convert the text to upper case.

Sub UsingRange()

Dim rng As Range

For Each rng In Selection

    rng.Value = UCase(rng.Value)

Next

End Sub

Learn Excel VBA today

This tutorial gave you a strong foundation on how the Excel VBA Range object can be used along with some useful VBA techniques. Fast-track your learning with our online Excel Macros course containing 41 practical tutorials to learn to create powerful macros that make Excel work for you.