What is 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 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.
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:
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:
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 4th 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:
