Excel VBA: Get Range of Cells with Values: 7 Methods

Method 1 – Get Value of a Single Cell in Excel

Step 1:

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:

Enter the VBA application window. Choose Module from the Insert tab.

Write VBA code on this command module window.

Step 3:

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.

Get the value of cell B7 as mentioned in the code. Perform this task with an alternate code.

Alternative Code to Get Value of a Single Cell

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.

Method 2 – Get Values of All Cells in 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.

Step 3:

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.

Method 3 – Print Values of a Range at 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:

Press F5, which will run the following code.

Print the values at Range B5:B9 from its adjacent cells.

Method 4 – Get Values of Specific Cells from a 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.

Get values from a specific cell of the chosen range.

Method 5 – Get Values of Cells from Selection Using Excel VBA

Step 1:

Enter the VBA command module. Press Alt+F11 for that.
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.

Get the value of the cell from our selection.

Method 6 – Apply VBA User Input Method to Get Values of Cells

Step 1:

Enter the command module. 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. Range B5:D9.

Step 3:

Press OK.

Method 7 – Get Values at an Immediate Window

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