Excel VBA: Get Range of Cells with Values (7 Examples)

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

7 VBA Examples to Get Range of Cells with Values in Excel

We will discuss 7 Excel VBA examples 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.

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.

Similar Readings

• Excel VBA: Copy Dynamic Range to Another Workbook
• Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)
• VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

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.

Read More: Excel VBA: Loop Through Columns in Range (5 Examples)

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 (5 Methods)

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.

Read More: How to Use VBA to Select Range from Active Cell in Excel (3 Methods)

Similar Readings

• Excel VBA to Loop through Range until Empty Cell (4 Examples)
• How to Convert Range to Array in Excel VBA (3 Ways)

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.

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. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Use Range with Variable Row and Column with Excel VBA
• Excel VBA to Select Used Range in Column (8 Examples)
• VBA to Loop Through Rows in Range in Excel (6 Examples)
• How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)
• Use VBA for Each Row in a Range in Excel