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

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

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

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.

Then 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, 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: