Excel / VBA Range - Learn Using with 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.

In this tutorial: hide

1. Declaring a range by Dim and Set
2. Using For..Each to iterate through all cell values and show in the message box
3. The example of setting the value of cells by using Range
4. Working with VBA Range and Interior colors of cells
5. Assigning 56 default colors to Excel cells by Range
6. Assigning cell values to an array example
7. Display whole array example
8. An example of Range’s Select method
9. An example of Rows property of the Range object
10. Columns property of Range Object example
11. Count property to get the total number of cells, rows, columns in the range
12. FAQs for VBA Range

Declaring a range by Dim and Set

Dim rng As Range

Set rng = Range(A2:D4)

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 cell 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:

Sub range_ex()

    MsgBox Range("B2").Value

    MsgBox Range("C2").Value

    MsgBox Range("D2").Value

End Sub

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 VBA 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:

Sub range_ex()

Dim SheetRange As Range

Dim cell As Range

Dim all_cells As String

'Creating range of cells

Set SheetRange = Range("B2:B7")

'For Each loop to get all celss values

For Each cell In SheetRange

    all_cells = all_cells & cell & vbNewLine

Next cell

'Display all cells in the Range

    MsgBox all_cells

End Sub

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:

Sub range_ex()

Range("E1").Value = "Quantity"

Range("E2").Value = "20 Bags"

Range("E3").Value = "30 Bags"

Range("E4").Value = "3 Bags"

Range("E5").Value = "1 bag"

End Sub

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:

Sub range_ex()

  Range("A2").Interior.ColorIndex = 15

  Range("B2").Interior.ColorIndex = 16

  Range("C2").Interior.ColorIndex = 31

  Range("D2").Interior.ColorIndex = 38

  Range("E2").Interior.ColorIndex = 56


End Sub

Output:

Assigning 56 default colors to Excel cells by Range

As ColorIndex supports 56 default color palettes, 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:

Sub range_ex()

Dim rng As Range, cell As Range

Dim i As Integer

i = 1

'56 cells to hold all default palette colors

Set rng = Range("A1:G8")

For Each cell In rng

    cell.Interior.ColorIndex = i

    cell.Value = i

    i = i + 1

Next cell

End Sub

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:

Sub range_ex()

    Dim arr_prods As Variant

    arr_prods = Range("B1:D4").Value

    'Accessing Array elements in message box

    MsgBox arr_prods(1, 1)

    MsgBox arr_prods(1, 2)

    MsgBox arr_prods(1, 3)


End Sub

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.

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

Code:

Sub range_ex()

Dim arr_prods As Variant

Dim who_arr As String

Dim i As Integer

'2D array from Excel sheet

arr_prods = Range("B1:D4").Value

For i = 1 To UBound(arr_prods)

   who_arr = who_arr & arr_prods(i, 1) & "    " & arr_prods(i, 2) & "    " & arr_prods(i, 3) & vbNewLine

Next i


'Displaying whole array in Message Box
MsgBox who_arr

End Sub

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:

Sub range_ex()

Dim rng As Range

Set rng = Range("A1:D4")

rng.Select

End Sub

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:

Sub range_ex()

Dim rng As Range

Set rng = Range("A1:D4")

'Setting background color of row number 2

rng.Rows(2).Interior.ColorIndex = 17

End Sub

Output:

Columns property of Range Object example

Similarly, VBA 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:

Sub range_ex()

Dim rng As Range

Set rng = Range("A1:F10")

'Setting background color of column number 4

rng.Columns(4).Interior.ColorIndex = 42

rng.Select

End Sub

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:

Sub range_ex()

Dim rng As Range, Str As String

Set rng = Range("A1:D10")


rng.Select

Str = "Number of Cells = " & rng.Count & vbNewLine

Str = Str & "Number of Rows = " & rng.Rows.Count & vbNewLine

Str = Str & "Number of Columns = " & rng.Columns.Count


MsgBox Str

End Sub

Result:

FAQs for VBA Range

What is the Range object in VBA?
The Range object in VBA is used to select a range of cells in an Excel sheet. It allows manipulation of cell values, formatting, and various other operations.
How do I declare and set a Range in VBA?
You can declare a Range using the "Dim" statement and set it using the "Set" keyword. For example:

Dim rng As Range

Set rng = Range("A1:D4")
What are some basic operations with the VBA Range object?
Basic operations with the VBA Range object include getting and setting cell values, changing interior/background colors, modifying border colors, and more.
Can I use VBA Range to iterate through cell values?
Yes, you can use a "For Each" loop with the VBA Range object to iterate through cell values in a specified range.
How do I set the values of cells using the VBA Range object?
You can set the values of cells using the VBA Range object by assigning values directly to the cells. For example:

Range("E1").Value = "Quantity"

Range("E2").Value = "20 Bags"
How can I change the interior/background color of cells with VBA Range?
You can change the interior/background color of cells using the Interior.ColorIndex property of the VBA Range object.
Is it possible to assign default colors to cells using VBA Range?
Yes, you can assign default colors to cells by using a loop and the Interior.ColorIndex property, as ColorIndex supports a palette of 56 default colors.
How can I work with VBA Range and arrays?
You can assign cell values to arrays using the VBA Range object. Then, you can access and manipulate array elements as needed.
What is the purpose of the VBA Range Select method?
The VBA Range Select method is used to select a range of cells in an Excel sheet. It highlights the specified cells.
How do I use the Rows property of the VBA Range object?
The Rows property of the VBA Range object allows you to select and perform actions on specific rows within a range.
Can I use the Columns property of VBA Range for specific column operations?
Yes, the Columns property of the VBA Range object enables you to select and perform actions on specific columns within a range.
How can I get the total count of cells, rows, and columns in a VBA Range?
You can use the Count property of the VBA Range object to obtain the total count of cells, rows, and columns within a specified range.