merge multi-dimensional arrays

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
I have a very tough time with the redim function and the multi-dimensional arrays. I'm trying to merge two multi-dimensional arrays into one. I know that the second dimension of each array will always be 2, but the first dimension will change. When I use the redim function I do not get an error message but it erases everything that was in the first array in the first place. When I use redim preserve I get a subscript out of range. Help.

Code:
Function merge_arrays2(first_array As Variant, sec_array As Variant) As Variant


Dim i As Integer, j As Integer, m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim first_array(m + j, 2)


For i = 1 To UBound(sec_array)
j = j + 1
first_array(j, 1) = sec_array(i, 1)
first_array(j, 2) = sec_array(i, 2)
Next


merge_arrays2 = first_array


End Function
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
when using Redim Preserve, you may only redimension the last dimension of the array, in your case you are trying to redimension the first dimension hence you get an error, you can only use redim preserve to redimension the second dimension
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
ReDim Preserve only works when the last dimension is being changed.

Code:
Dim myArray()
Redim myArray(1 to 3, 1 to 5) 
Redim Preserve myArray(1 to 3, 1 to 10) ' works
Redim Preserve myArray(1 to 10, 1 to 10) ' doesn't
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
you could do something like:

passing a 3rd array which will be the merged one


Code:
Function MergeArrays(firstArray As Variant, secondArray As Variant, _
                        resultArray As Variant) As Boolean
Dim NumElements1 As Long, NumElements2 As Long, Ndx As Long, Cntr As Long


MergeArrays = False
If Not IsArray(firstArray) Then Exit Function
If Not IsArray(secondArray) Then Exit Function


' to be more robust here you could test that
' the first two arrays are allocated and are 2 dimensional
' i'll leave this out though, resultArray needs to be dynamic as well


NumElements1 = UBound(firstArray, 1) - LBound(firstArray, 1) + 1
NumElements2 = UBound(secondArray, 1) - LBound(secondArray, 1) + 1


ReDim resultArray(1 To NumElements1 + NumElements2, 1 To 2)
Cntr = 1


For Ndx = LBound(firstArray, 1) To UBound(firstArray, 1)
    resultArray(Cntr, 1) = firstArray(Ndx, 1)
    resultArray(Cntr, 2) = firstArray(Ndx, 2)
    Cntr = Cntr + 1
Next Ndx


For Ndx = LBound(secondArray, 1) To UBound(secondArray, 1)
    resultArray(Cntr, 1) = secondArray(Ndx, 1)
    resultArray(Cntr, 2) = secondArray(Ndx, 2)
    Cntr = Cntr + 1
Next Ndx


MergeArrays = True
End Function
 

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550

ADVERTISEMENT

Thanks. Merge into a third array. That did it.

Code:
Dim i As Integer, j As Integer, k As Integer, third_array(), m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim third_array(m + j, 2)


For i = 1 To UBound(first_array)
k = k + 1
third_array(k, 1) = first_array(i, 1)
third_array(k, 2) = first_array(i, 2)
Next


For i = 1 To UBound(sec_array)
k = k + 1
third_array(k, 1) = sec_array(i, 1)
third_array(k, 2) = sec_array(i, 2)
Next


merge_arrays2 = third_array
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
In addition to using a 3rd array as has already been suggested, you can also use Excel's TRANSPOSE function to make the 1st dimension the 2nd dimension, then use Redim Preserve, and then transpose again. The transpose function does have a limit of how large the array can be and you would have to test if your array fits in that limit.

You can also consider an array of arrays approach as in Preserve existing content while resizing any dimension of a matrix
I have a very tough time with the redim function and the multi-dimensional arrays. I'm trying to merge two multi-dimensional arrays into one. I know that the second dimension of each array will always be 2, but the first dimension will change. When I use the redim function I do not get an error message but it erases everything that was in the first array in the first place. When I use redim preserve I get a subscript out of range. Help.

Code:
Function merge_arrays2(first_array As Variant, sec_array As Variant) As Variant


Dim i As Integer, j As Integer, m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim first_array(m + j, 2)


For i = 1 To UBound(sec_array)
j = j + 1
first_array(j, 1) = sec_array(i, 1)
first_array(j, 2) = sec_array(i, 2)
Next


merge_arrays2 = first_array


End Function
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Just for fun, , not very practical. Redim both dimensions in 1 go:

Code:
Sub ReDim2D()
Dim vArray

ReDim vArray(1 To 10, 1 To 2)

vArray(2, 1) = "B"
vArray(1, 2) = 5

' like ReDim Preserve (1 to 15, 1 o 3)
vArray = Application.IfError(Application.Index(vArray, [row(1:15)], Array(1, 2, 3)), Array("", 0, False))

vArray(12, 1) = "L"
vArray(13, 2) = 12.3456
vArray(14, 3) = True

End Sub
 
Last edited:

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
thanks tusharm and pgc the helpful suggestions. I really want to thank all of your for helping me out.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
For what it's worth, many times you can greatly simply things by not using arrays. In my experience, they are often used when they are really not needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

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