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

Get FREE Advanced Excel Exercises with Solutions!

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.

 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.


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.


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


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.


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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo