Range Data Into Array Excel VBA Macro

Arrays are used to store sets of data. A range with data in Excel can be stored in a two-dimensional (2D) array. A column or row are also Range objects and have by default 2 dimensions (one of which has only one element – either the column or the row). In this post we see how to put data from a range into a 2D array, and data from a single column or row into a 1D array.

Macro code:

  Sub RangeToArray()

      Dim rng As Range, arr2D As Variant
      Set rng = Selection
      arr2D = rng.Value
 
      Dim col1D As Variant
      Set rngcol = rng.Columns(2)  'column 2 in the selected range
      col1D = Application.Transpose(rngcol)  'get value with col1D(index)
 
      Dim row1D As Variant
      Set rngrow = rng.Rows(1)  'row 1 in the selected range
      row1D = Application.Transpose(Application.Transpose(rngrow.Value))
 

      'Example loop through 1D array values
      For Each elem In col1D  
          MsgBox elem
      Next elem

  End Sub

Macro explained:

• We declare variables for the target range (rng) and the array (arr2D). The target range in the example above is the selection. That can be changed to any specific defined range, for example with Range("A1:D4").
• We can simply put the data from a range into an array assigning the value of the range to the array variable with a single line of code (arr2D = rng.Value). This generates a 2D array that has rows in the first dimension and columns in the second dimension. We can retrieve a value in the array specifying the value for each dimension. For example, we can get the value in column 2 (Username) for row 10 as arr2D(10)(2).
• Similarly, we can loop through elements in the 2D array specifying the row and column in the parenthesis. For example, we could loop through column 2 (Username) to get the name of each user with a For loop (For r = 2 To 16) and arr2D(r)(2). We could use that loop to create a new array with only 1 dimension. But there is an easier way to do it using Transpose.
• We take column 2 of the target range as an example and put the data into an array using Transpose. That creates a 1D array with as many elements as cells with data within that column. We can retrieve a value in the array specifying the index in the parenthesis, for example col1D(5).
• Next we take row 1 of the target range as an example and put the data into an array using Transpose twice. Note it is slightly different now for rows, compared to how it’s done for columns, we have to use Transpose twice. That also creates a 1D array with as many elements as cells along that row in the selected range (or any target range).
• The last part of the code shows how to loop through the elements in a 1D array using a For Each loop. We could also use a regular For loop from 1 to the upper bound of the 1D array (using Ubound function) and get each element or value using col1D(index). Note that the index for the first element is 1 and not 0 (as often in arrays) because the column or row index starts at 1.

This is how we put data from a range, column, or row into an array in Excel VBA.

 

Other examples:

• How to Add Sheets From Selection with Excel VBA macros
• How to Add Multiple Series To Chart with Excel VBA macros
• How to Extract Emails From Outlook with Excel VBA macros