# Convert 2 dimensional array to 1 dimensional array

#### RagnarokX66

##### New Member
I have a 2 dimensional array with 4 Fields and a dynamic number of total records. I will ultimately need a method to return the number of rows for this array but for now I can assume it is a fixed value, 30.

I am trying to create a new array with 1 dimension that merges the values of the 2 dimensional array... each row of the two dimensional array is to become a single element of the one dimensional array.

I have tried the following with no success:

For j = 0 To 29
ArrayD2(j) = ArrayD(0, j) & ArrayD(1, j) & ArrayD(2, j) & ArrayD(3, j)
Next j

Can anyone help?

Thanks

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I have a 2 dimensional array with 4 Fields and a dynamic number of total records. I will ultimately need a method to return the number of rows for this array but for now I can assume it is a fixed value, 30.

I am trying to create a new array with 1 dimension that merges the values of the 2 dimensional array... each row of the two dimensional array is to become a single element of the one dimensional array.

I have tried the following with no success:

For j = 0 To 29
ArrayD2(j) = ArrayD(0, j) & ArrayD(1, j) & ArrayD(2, j) & ArrayD(3, j)
Next j
You should have shown us the declaration for ArrayD as well as how its elements are assigned to it. Assuming the normal method of creating the array (that is, it is a Variant array which was assigned a range of values to create it, your code should look more like this...
Code:
``````Redim ArrayD2(0 To UBound(ArrayD) - 1)
For R = 1 To Ubound(ArrayD)
ArrayD2(R - 1) = ArrayD(R, 1) & ", " & ArrayD(R, 2) & ", " & ArrayD(R, 3) & ", " & ArrayD(R, 4)
Next``````
These kinds of variant arrays have a lower bound of 1, not 0, and the row indexes are first with the column indexes second. Note that I also assumed you really meant it when you showed ArrayD2 having a lower bound of 0 (hence the subtraction of 1). Oh, and for future questions you might ask, please do not simplify your setup/problem for us... all that does is make things more complicated.

You should have shown us the declaration for ArrayD as well as how its elements are assigned to it. Assuming the normal method of creating the array (that is, it is a Variant array which was assigned a range of values to create it, your code should look more like this...
Code:
``````Redim ArrayD2(0 To UBound(ArrayD) - 1)
For R = 1 To Ubound(ArrayD)
ArrayD2(R - 1) = ArrayD(R, 1) & ", " & ArrayD(R, 2) & ", " & ArrayD(R, 3) & ", " & ArrayD(R, 4)
Next``````
These kinds of variant arrays have a lower bound of 1, not 0, and the row indexes are first with the column indexes second. Note that I also assumed you really meant it when you showed ArrayD2 having a lower bound of 0 (hence the subtraction of 1). Oh, and for future questions you might ask, please do not simplify your setup/problem for us... all that does is make things more complicated.

Thanks Rick this was very helpful

ArrayD is publicly declared and filled out by a query- it is generated by the GetRows method of the ADO recordset object and varies as different queries are run in the program. It will always be two dimensional and always have 4 fields but the number of records will vary 'rapidly.' So I need a way to loop through all the 'rows' in ArrayD and create the elements for ArrayD2. The code you supplied- while functional- will only let me loop through 4 records of ArrayD.

Do you know of a way to quickly get a number for the number of 'rows' in a two dimensional array?

Thanks for your continued help

ArrayD is publicly declared and filled out by a query- it is generated by the GetRows method of the ADO recordset object and varies as different queries are run in the program. It will always be two dimensional and always have 4 fields but the number of records will vary 'rapidly.' So I need a way to loop through all the 'rows' in ArrayD and create the elements for ArrayD2. The code you supplied- while functional- will only let me loop through 4 records of ArrayD.

Do you know of a way to quickly get a number for the number of 'rows' in a two dimensional array?
In that case, I think this code will work for you (note the ,2 for the UBound function call to get to the 2nd dimension of the 2-dimensional array)...
Code:
``````ReDim ArrayD2(0 To UBound(ArrayD, 2))
For R = 0 To UBound(ArrayD, 2)
ArrayD2(R) = ArrayD(1, R) & ", " & ArrayD(2, R) & ", " & ArrayD(3, R) & ", " & ArrayD(4, R)
Next``````

In that case, I think this code will work for you (note the ,2 for the UBound function call to get to the 2nd dimension of the 2-dimensional array)...
Code:
``````ReDim ArrayD2(0 To UBound(ArrayD, 2))
For R = 0 To UBound(ArrayD, 2)
ArrayD2(R) = ArrayD(1, R) & ", " & ArrayD(2, R) & ", " & ArrayD(3, R) & ", " & ArrayD(4, R)
Next``````

Thanks Rick, this did the job.

Thanks Rick, this did the job.

Rick, Maybe you have some insight on this matter:

Regarding ArrayD- I had been connecting to another excel workbook that contained the data that was being queried to populate ArrayD with getrows(). This action occured in a function procedure where I would pass one of the function arguments to the SQL string. This worked perfectly but it ran a bit slow so I became interested in changing the source data to a csv file rather than an excel work book.

I find I am having a problem passing the funtion argument to the sql string- the procedure works fine if I hard code the sql completely (if I remove arg1 from the sql string and replace it with an actual value.)

If I convert the function to a sub and run it I get the error "operation is not allowed in this context." This occurs at the getrows() line.

Any thoughts?

Code below

Code:
``````Public Function DataValidation(arg1 As Range)

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim Pm As ADODB.Parameter
Dim Cm As ADODB.Command
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Greg\Documents\TestData\;Extended Properties=""text;HDR=YES;FMT=Delimited"";Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from TestData.csv where PropID ='" & arg1 & "'"

Set oRS = New ADODB.Recordset
oRS.Open SQL, oCn, 3, 3

With oRS
.MoveLast
.MoveFirst
i = .Fields.Count
j = .RecordCount
End With
ArrayD = oRS.GetRows(j)

oCn.Close

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

DataValidation = WorksheetFunction.Transpose(ArrayD)

End Function``````

Replies
6
Views
158
Replies
1
Views
184
Replies
3
Views
305
Replies
7
Views
123
Replies
4
Views
288

1,203,524
Messages
6,055,905
Members
444,832
Latest member
bgunnett8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back