Convert 2 dimensional array to 1 dimensional array

RagnarokX66

New Member
Joined
Sep 11, 2011
Messages
36
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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.
 

RagnarokX66

New Member
Joined
Sep 11, 2011
Messages
36
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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
 

RagnarokX66

New Member
Joined
Sep 11, 2011
Messages
36
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.
 

RagnarokX66

New Member
Joined
Sep 11, 2011
Messages
36
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

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?

Disable AdBlock

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
Top