Convert 2D Arary To 1D or List – Excel VBA Macro – Quickest Method
Convert Array of 2 Dimension to 1 Dimension
In Excel VBA , we use 2D arrays quite often. This is because, we convert Excel Worksheet Range to Array and do the data list or string operations on the list. In most cases it will be a 2D array.
While converting a 2D array to 1D, we can do it 2 ways. Either convert all the Rows in to a list or Columns into list. Both these can be achieved by using the function mentioned in this code.
Not only converting, this code will help you in understanding how to get only a row or column from a 2D array without using looping thru each element. It saves some time.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | Sub Convert_2D_To_1D_Array() Dim rng As Range, arr As Variant 'Convert Rows to 1D Set rng = ThisWorkbook.Sheets("Sheet2").Range("A1") arr = Array_2D_To_1D("R") Set rng = rng.Resize(1, UBound(arr, 1) + 1) rng = arr 'Convert Columns to 1D Set rng = ThisWorkbook.Sheets("Sheet2").Range("A2") arr = Array_2D_To_1D("C") Set rng = rng.Resize(1, UBound(arr, 1) + 1) rng = arr End Sub Function Array_2D_To_1D(cr As String) As Variant 'Declare Array as variant & Range Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double Dim d1Arr As Variant, app As Object, sStr As String 'Initialize Range to Reference a portion of Worksheet Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:D3") Set app = Application 'Assign Range to Array rArray = rRange.Value sStr = "" 'Convert to 1D Array - By Row If cr = "R" Then For iRow = 1 To UBound(rArray, 1) d1Arr = app.Index(rArray, iRow, 0) If sStr = "" Then sStr = Join(d1Arr, ",") Else sStr = sStr & "," & Join(d1Arr, ",") End If Next iRow End If 'Convert to 1D Array - By Column If cr = "C" Then For iCol = 1 To UBound(rArray, 2) d1Arr = app.Transpose((app.Index(rArray, 0, iCol))) If sStr = "" Then sStr = Join(d1Arr, ",") Else sStr = sStr & "," & Join(d1Arr, ",") End If Next iCol End If 'Display 2D array converted to 1D MsgBox sStr d1Arr = VBA.Split(sStr, ",") Array_2D_To_1D = d1Arr End Function |
You can opt to use only one method rather than using both row/column slicing method
Slice 2D Array Elements Quickly
Here is an example for the two methods:
2D Array – Range(“A1:D3”) – Array Size (3,4)
| 2D Array | Col1 | Col2 | Col3 | Col4 |
| Row1 | a | b | c | d |
| Row2 | 1 | 2 | 3 | 4 |
| Row3 | Excel | Macro | VBA | Array |
2D to 1D Array – By Converting each Row
| a | b | c | d | 1 | 2 | 3 | 4 | Excel | Macro | VBA | Array |
2D to 1D Array – By Converting each Column
| a | 1 | Excel | b | 2 | Macro | c | 3 | VBA | d | 4 | Array |
Note that from the 2nd value on, each of these 1D array differs. In the first one, it reads the “a,b,c,d” the first row, then 2nd row and goes on. In second 1D Array, it the columns values “a,1,Excel”, then next column and so on.
It up to your needs to use on these methods and not both. Similar to a range, the same method can also be used to convert a table or named range into an Array.
The .resize function used in the code is mandatory to tell Excel what would be the exact size of the array that we are writing into worksheet. So, we initialize a range to address “A1”. Then, we resize it as per the size of the array using the comamnd UBound(Arr,1). This gives the number of items in the 1D Array.