VBA Read Values from Range to an Array in Excel. We can read values from Range, Cell, or Table to Arrays. Using Range we can read multiple values from one column or from multiple columns or rows. Cell can contain either single value or multiple values. Table also can consists of multiple values in multiple columns or rows. Let us see how to read values from range in different ways in the following tutorial.
Macro code to Read Values from Range(Single Column) to an Array
Here is the example macro code to Read Values from Range(Single Column) to an Array. In this code we have used range object to define range, UBound is used to set array upper bound and Array is used to store read values from range. It is an example for single dimensional array.
'Read Values from Range(Single Column) to an Array Sub VBA_Read_Values_from_Range_to_Array_Single_Column() 'Variable Declaration Dim aArrayList() As Variant Dim iRowNum As Integer, iColNum As Integer 'Assign range to a variable aArrayList = Range("B1:B5") 'Loop Through Rows For iRowNum = 1 To UBound(aArrayList) 'Display result in Immediate window Debug.Print aArrayList(iRowNum, 1) Next iRowNum End Sub
Output :You can see output on the immediate window. Here is the screenshot of the above macro code output.
VBA code to Read Values from Range(Multiple Columns) to an Array
Let us see the example macro code to Read Values from Range(Multiple Columns) to an Array. It is an example for Multi dimensional array.
'Read Values from Range(Multiple Columns) to an Array Sub VBA_Read_Values_from_Range_to_Array_Multiple_Columns() 'Variable Declaration Dim aArrayList() As Variant Dim iRowNum As Integer, iColNum As Integer 'Assign range to a variable aArrayList = Range("A1:D4") 'Loop Through Rows For iRowNum = 1 To UBound(aArrayList, 1) 'Loop Through Columns For iColNum = 1 To UBound(aArrayList, 2) 'Display result in Immediate window Debug.Print aArrayList(iRowNum, iColNum) Next iColNum Next iRowNum End Sub
Output : You can output on the immediate window. Here is the screenshot of output.
Macro code to Read Values from Range(Single Cell) to an Array
Here is the example macro code to Read Values from Range(Single Cell) to an Array.
'Read Values from Range(Single Cell) to an Array Sub VBA_Read_Values_from_Range_to_Array_Single_Cell() 'Variable Declaration Dim aArrayList() As Variant Dim rRange As Range Dim iRowNum As Integer, iColNum As Integer 'Define Cell Range Set rRange = Range("A1") 'ReDefine Array size ReDim aArrayList(1 To 1, 1 To 1) 'Store Cell Range to An Array aArrayList(1, 1) = rRange 'Display Message on the Screen Debug.Print rRange End Sub
Output : You can output on the immediate window. Here is the screenshot of output.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers