Excel/VBA Range [11 Examples]

Range in VBA

• VBA has a Range object that can be used to select the range of cells in an Excel sheet.
• You may use the Range object to get the cell’s value.
• You may set the new values to the cells.
• The Interior/background colors of cells can also be set by using the Range object.
• The border colors of the cells can also be changed.
• And so many other things can be done – let us have a look at examples of using VBA Range object.

We will start with simple, just displaying the value of a cell, to a range of cells with For..Each loop. We will set the borders, interior colors, and examples also covers using range with arrays.

Declaring a range by Dim and Set

An example of getting the values using a Range object

In the first example of using Range, we will use the Value property of the Range object to get the value of the Sheet cells. Then we will display a few cells values in the message box.

For that, we have the following Excel sheet with some dummy data of Product Information for the demos only.

VBA code:

Result:

First message box:

Using For..Each to iterate through all cell values and show in the message box

In this example, we created a range of cells – B2 to B7.

We will use a For..Each loop and iterate through all cell values in the range.

In each iteration, we will assign the cell value to a string variable, concatenate all and add a line break.

Finally, the result is displayed in the message box:

VBA code with Range of cells:

Result:

The example of setting the value of cells by using Range

In this example, we will set the values of a few cells by using the Range object.

In the Sample sheet above, you can see the E-column cells are empty.

We will assign “Quantity” as the header and set values in E2 to E5 cells as follows:

Code:

Output:

Working with VBA Range and Interior colors of cells

For this example, we will change the interior/background colors of the cells by using the Range object.

For that, Range.Interior.ColorIndex property is used as shown in the code and output below:

VBA code:

Output:

Assigning 56 default colors to Excel cells by Range

As ColorIndex supports 56 default colors palette, we will create a range of 56 cells.

This is followed by using a for loop and assigning a different color to each cell in the range.

VBA code:

Output:

Learn more about VBA Interior/Background Color

Assigning cell values to an array example

For this example, we will work with Range and arrays in VBA.

A two-dimensional array is created and its elements are cell values from our sample sheet.

We will display a few element values to show how it worked.

In the next example, for loop is used to iterate through all array elements

Code:

Output:

MsgBox 1:

MsgBox 2:

While the third message box with (1,3) displays the “Status” header.

Display whole array example

We are using the same array as in the above example.

Then For loop is used to iterate through all elements in the array.

Code:

Output:

An example of Range’s Select method

To select a range of cells in the Excel sheet, you may use the Select method.

An example with output:

Output:

You can see, the cells in the range are selected.

An example of Rows property of the Range object

After creating a range, if you want to select a specific row and perform some action, you may use the Rows property of the Range object.

For example:

rng.Rows(3).Interior.ColorIndex

In the example below, we will set the background/interior color of row number 2.

VBA program:

Output:

Columns property of Range Object example

Similarly, Range.Columns property can be used to select a specific column within the range and perform the desired task.

In the example below, we will set the Interior color of 4^th column:

Output:

The selected area shows our range cells (by using the Select method) while the colored area is set by using the Columns property.

Count property to get the total number of cells, rows, columns in the range

To get the total count of the cells, columns, and rows in the range, you may use the Count property of the Range object.

In the example below, we created a range and displayed the total number of cells, rows, and columns:

Result: