Convert 2 dimensional array to 1 dimensional array

[RagnarokX66]

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

 

[Rick Rothstein]

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...

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.

 

[RagnarokX66]

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...

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

 

[Rick Rothstein]

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)...

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

 

[RagnarokX66]

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)...

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.

 

[RagnarokX66]

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

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