Excel VBA Range: How to Use Range in VBA

Every Excel user is familiar with selecting a range of cells – by SHIFT + Clicking, CTRL + Clicking, or dragging the mouse over a range of cells.

But how does one select a range in VBA? In this article, I’ll guide you on how to select a range of cells and activate a particular cell in that range. I’ll also show you practical examples on how you can do this in your Excel workbooks. For a more in-depth discussion on selecting ranges in VBA, consider taking this course on Visual Basic for MS Excel.

What is Range?

A range, as you might have guessed, refers to a cell or a range of cells. A range can be of three types:

• One cell (even a single cell selection is called a range)

• Multiple cells

• Multiple non-contiguous cells – the same as CTRL + Clicking cells

Every cell selected in the range must be within the same worksheet. As of now, Excel does not support multi-dimensional ranges (i.e. ranges spread over multiple worksheets).

You will use the Range function a lot in your VBA programming since it makes selecting a cell or a range of cells so effortlessly easy.

Syntax

The basic syntax of the VBA range command is as follows:

Range(Cell 1. Cell 2)

Where

Cell 1 (required) = The actual range/cell to be acted on. This should be a specific cell name (“A1”) or a range of cells (“A1: A10”).

Cell 2 (optional) = The top-left or bottom-right of the cell range to be selected.

Range can accept not just cell names and ranges, but also strings:

Range(“SalesVolume”)

This will select cells which have the value of “SalesVolume”.

You can even nest ranges inside each other and select specific strings:

Range(“A1:A20”, Range(“SalesVolume”))

This will refer to all the cells in the range A1:A20 that hold the value “SalesVolume”

To see how it works, let’s take a look at a few examples:

Example 1: Inserting a Value into a Selected Range

Let’s say we want to fill the first ten rows in column A with the value “Hello”. To do this, we will add te following code to our VBA macro:

Worksheets(“Sheet1”).Range(“A1:A10”).Value = “Hello”

Where,

Worksheets(“Sheet1”) = Name of active worksheet

.Range(“A1:A10”) = Range of cells to be selected

.Value = “Hello” = Value to be inserted in the cells

The result:

We can achieve the same result by typing the following:

Worksheets(“Sheet1”).Range(“A1”,”A10”).Value = “Hello”

Note: The ‘Worksheet(“Sheet1”)’ part isn’t really required if you are working with just one sheet, but is a necessity in any multi-sheet workbook.

Confused? Check out this comprehensive course on Excel, macros and VBA and clear your doubts.

Example 2: Inserting a Formula in a Range of Cells

Let’s say we have a range of values in a spreadsheet like this:

(We actually populated this spreadsheet using the .Range method described above).

We can use the following formula to calculate the sum of all these values:

Range(“C1”).Formula = “=sum(A1:B10)”

Notice how we use .Formula instead of .Value.

The result:

Example 3: Changing Formatting in a Range of Cells

The Range function isn’t useful for adding formulas and inserting values alone. It can also be a useful tool for changing the values of specific cells.

Here, we will change the formatting in a range of cells to Bold. This is the original worksheet:

Here’s the formula we’ll use:

And here’s the result:

The important thing to note here is how we’ve used the two cell ranges to select only cells C3:C5. The first cell range (Cells(3,3)) specifies that only cells from the third column are to be selected. If we wanted to select the first column, we would write Cells(1,1).

The second cell range specifies the rows. It goes from the higher cell number to the lower cell number ((5,3) not (3,5)).

Using Excel 2007? This course will get you up to speed on using VBA in Excel 2007.

Example 4: Using the Offset Method

Another way to select a range is to use the Offset method. The basic syntax looks like this:

Range(“A1”).Offset(2,3).Value = 4

This will change the value of the cell two cells below, and three cells to the right of A1 to 4.

Let’s use it in a spreadsheet and see what the result is:

This makes the cell E3 (two cells down, three cells right – offset(2,3)) = 4

The Offset method is particularly useful with variables.

Let’s create a little program that will populate a range of rows and columns with all odd numbers from 1 to 50.

Sub macro_1()

Num = 1

For Row = 0 To 4

For Col = 0 To 4

Range(“A1”).Offset(Row, Col).Value = Num

Num = Num + 2

Next Col

Next Row

End Sub

Here, we’re starting from number 1 and referring to the first five rows and columns (remember that Excel counts from 0, which is inclusive). Instead of specifying a particular row or column, we’ve used two variables with Offset. We’ve also increased the number by 2 within our loop to create a progression.

The end result looks like this:

This example just goes to show how useful the Range command can be.

Besides all these features, you can also use Range to select a particular cell:

Range(“A1”).Select

Or to make a particular cell range active:

Range(“A1:A20”).Activate

You’ll turn to these functions regularly in your VBA programming. To find out more about Range and other similar advanced VBA features, take this foundation course on Excel, macros, and Visual Basic for Applications.

Any tips and tricks on using Range? Let us know in the comments below!