In Excel, we store data in different cells. We can call a cell from a range to get data from that specific cell. This can be performed in different ways. But here, we will show how to get a range of cells with values using Excel VBA.
Excel VBA to Get Range of Cells with Values: 7 Examples
We will discuss 7 examples of Excel VBA to get a range of cells with values here.
The following dataset will be used for this purpose.
1. Get Value of a Single Cell in Excel
In this section, we want to get the value of a single cell from the dataset. For this, we will use the Excel VBA Range function.
Step 1:
- First, go to the Sheet Name section at the bottom border of each cell.
- Press the right button of the mouse.
- Click the View Code option from the list.
Step 2:
- We enter the VBA application window. Choose Module from the Insert tab.
- We will write VBA code on this command module window.
Step 3:
- Now, copy and paste the following VBA code into the command module.
Sub cell_with_value_1()
Dim value_1 As Variant
value_1 = Range("B7").Value
MsgBox value_1
End Sub
Step 4:
- Press F5 to run the code.
We get the value of cell B7 as mentioned in the code.
We can perform this task with an alternate code.
Alternative Code to Get Value of a Single Cell
In this example, we will apply the previous VBA code. But we change the variable type to get values.
Step 1:
- Enter the command module by pressing Alt+F11.
- Copy the following VBA code here.
Sub cell_with_value_5()
Dim cell_1 As Range
Set cell_1 = Range("B9")
MsgBox cell_1.Value
End Sub
Step 2:
- Hit F5 and run the VBA code.
2. Get Values of All Cells in a Range
This example will show how to get the values of all cells of a range.
Step 1:
- Press Alt+F11 to enter the command module.
- Put the following VBA code on that module.
Sub cell_with_value_2()
Dim value_1 As Variant
Dim row_1 As Long
Dim column_1 As Long
value_1 = Range("B5:B9").Value
For row_1 = LBound(value_1, 1) To UBound(value_1, 1)
For column_1 = LBound(value_1, 2) To UBound(value_1, 2)
MsgBox value_1(row_1, column_1)
Next column_1
Next row_1
End Sub
Step 2:
- Press F5 to run the code.
Step 3:
- Now, click OK on the result window and the next results will show gradually.
We have 5 cells in our defined range and get them one after another.
3. Print Values of a Range at Adjacent Cells
In this example, we will print the values of a range at the adjacent cells.
Step 1:
- Hit Alt+F11 to enter the command module.
- Copy the VBA code below and paste it into the module.
Sub cell_with_value_3()
Dim range_1 As Range
Set range_1 = Range("A5:A9")
Dim new_range As Range
For Each cell In range_1
cell.Value = cell.Value & " " & cell.Offset(0, 1).Value
Next cell
End Sub
Step 2:
- Now, press F5 which will run the following code.
Here, we print the values at Range B5:B9 from its adjacent cells.
4. Get Values of Specific Cells from a Range
In this example, we will select a range first. Then choose a cell from that range.
Step 1:
- Press Alt+F11 and enter the command module.
- Copy and paste the VBA command module.
Sub cell_with_value_4()
Dim range_1 As Range
Set range_1 = Range("B5:D9")
MsgBox range_1.Cells(2, 2).Value
End Sub
Step 2:
- Press F5 and run that code.
Here, we get values from a specific cell of the chosen range.
5. Get Values of Cells from Selection Using Excel VBA
We will choose a range by the selection method and get the values of cells from that selection.
Step 1:
- First, we will enter the VBA command module. Press Alt+F11 for that.
- Then copy and paste that VBA code.
Sub cell_with_value_6()
Dim range_1 As Range
Set range_1 = Selection
MsgBox range_1.Cells(2, 3).Value
End Sub
Step 2:
- Run the code by pressing the F5 button.
We get the value of the cell from our selection.
Read More: How to Use VBA to Set a Range Variable to Selection in Excel
6. Apply VBA User Input Method to Get Values of Cells
First, we will select the range from the user input method. Then, get the values of cells of a range.
Step 1:
- First, enter the command module. For that press Alt+F11.
- Copy the VBA code and paste it into the module.
Sub cell_with_value_7()
Dim range_1 As Range
Set range_1 = Application.InputBox("Select Range", Type:=8)
MsgBox range_1.Cells(3, 1).Value
End Sub
Step 2:
- Press F5 and run the code.
- A window will appear to select the range. We choose range B5:D9.
Step 3:
- Finally, press OK.
7. Get Values at an Immediate Window
We want to view the result in the Immediate Window of Excel VBA.
Step 1:
- Put the VBA code on the command module by pressing Alt+F11.
Sub cell_with_value_8()
Dim value_1 As Variant
Dim row_1 As Long
Dim column_1 As Long
value_1 = Range("B5:B9").Value
For row_1 = LBound(value_1, 1) To UBound(value_1, 1)
For column_1 = LBound(value_1, 2) To UBound(value_1, 2)
Debug.Print value_1(row_1, column_1)
Next column_1
Next row_1
End Sub
Step 2:
- Now, run the code by pressing the F5 button.
- Then, select Immediate Window from the View tab. or Press Ctrl+G.
Now, go to the Immediate Window and see the result.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we showed 7 examples of Excel VBA to get the range of cells with values. I hope this will satisfy your needs. You can give your suggestions in the comment box.
Related Articles
- VBA to Set Range in Excel
- Excel VBA: Set Range by Row and Column Number
- How to Use Range with Variable Row and Column with Excel VBA
- VBA Range with Variable Row Number in Excel
