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.

 Excel VBA to Get the Values of a Single Cell

Step 2:

  • We enter the VBA application window. Choose Module from the Insert tab.

 Excel VBA to Get the Values of a Single Cell

  • 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

 Excel VBA to Get the Values of a Single Cell

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

 Excel VBA to Get the Values of a Single Cell

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

Excel VBA to Get the Values of All Cells in a Range

Step 2:

  • Press F5 to run the code.

Step 3:

  • Now, click OK on the result window and the next results will show gradually.

Excel VBA to Get the Values of All Cells in a Range

We have 5 cells in our defined range and get them one after another.


Similar Readings


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

Print Values of a Range at Adjacent Cells with Excel

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

Get Value of Specific Cells from a Range in Excel

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

Get Values of Cells from Selection Using Excel VBA

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

Apply the User Input Method to Get Values of Cells in Excel

Step 2:

  • Press F5 and run the code.
  • A window will appear to select the range. We choose range B5:D9.

Apply the User Input Method to Get Values of Cells in Excel

Step 3:

  • Finally, press OK.

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


Similar Readings


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

Get Values at an Immediate Window of Excel VBA

Step 2:

  • Now, run the code by pressing the F5 button.
  • Then, select Immediate Window from the View tab. or Press Ctrl+G.

Get Values at an Immediate Window of Excel VBA

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo