Excel/VBA Range [11 Examples]
VBA has a Range object that can be used to select the range of cells in an Excel sheet. You may use Range object to get the cell’s value.
- 1. Range in VBA
- 2. Declaring a range by Dim and Set
- 3. Using For..Each to iterate through all cell values and show in the message box
- 4. The example of setting the value of cells by using Range
- 5. Working with VBA Range and Interior colors of cells
- 6. Assigning 56 default colors to Excel cells by Range
- 7. Assigning cell values to an array example
- 8. Display whole array example
- 9. An example of Range’s Select method
- 10. An example of Rows property of the Range object
- 11. Columns property of Range Object example
- 12. Count property to get the total number of cells, rows, columns in the range
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
1 2 3 |
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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 3 4 5 6 7 8 9 10 11 |
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 4th column:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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: