## Split 2D array into two equal arrays

YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Split 2D array into two equal arrays

Hello everyone
Suppose I have a 2D array with 7 rows and three columns and I need to split this 2d array into two 2d arrayys
The first output array would be 4 rows and three columns and the second output array would be 3 rows and three columns
Is it possible to implement that into a function? I mean that the UDF that would be used return two arrays ... HansV
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Split 2D array into two equal arrays

Why not? It's easy enough to create the required loops.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

I am talking about how to return two arrays from UDF, not about the loops. HansV
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Split 2D array into two equal arrays

You could return an array of arrays, but why would you want to do that?
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

Thanks a lot. No specific reason (Just for curiosity to see if it is possible to return multi-arrays from UDF)
Can you show me simple example? HansV
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Split 2D array into two equal arrays

I think it's useless, but here is an example:

Code: Select all

``````Function ArrayOfArrays(a(), n As Long)
Dim b(), c()
Dim i As Long
Dim j As Long
ReDim b(LBound(a, 1) To LBound(a, 1) + n - 1, LBound(a, 2) To UBound(a, 2))
For i = LBound(a, 1) To LBound(a, 1) + n - 1
For j = LBound(a, 2) To UBound(a, 2)
b(i, j) = a(i, j)
Next j
Next i
ReDim b(LBound(a, 1) To UBound(a, 1) - (LBound(a, 1) + n - 1), LBound(a, 2) To UBound(a, 2))
For i = LBound(a, 1) To UBound(a, 1) - (LBound(a, 1) + n - 1)
For j = LBound(a, 2) To UBound(a, 2)
b(i, j) = a(i + n, j)
Next j
Next i
ArrayOfArrays = Array(a, b)
End Function
``````
And an example of using the function:

Code: Select all

``````Sub Test()
Dim a(), r
Dim i As Long
Dim j As Long
Dim k As Long
a = Range("A1:C7").Value
r = ArrayOfArrays(a, 4)
For k = 0 To 1
For i = LBound(r(k), 1) To UBound(r(k), 1)
For j = LBound(r(k), 2) To UBound(r(k), 2)
Debug.Print k, i, j, r(k)(i, j)
Next j
Next i
Next k
End Sub``````
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

Can you explain to me why useless ..? and in that case what is the best approach?!! HansV
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Split 2D array into two equal arrays

I don't see any real-world use for this.
Regards,
Hans Doc.AElstein
BronzeLounger
Posts: 1227
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

### Re: Split 2D array into two equal arrays

YasserKhalil wrote:
30 Oct 2020, 09:49
.. why useless
One possible reason why Yasser,
You know how to get the sub arrays in one code line:

Code: Select all

``````Sub second_output_array_would_be_3_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
Let a() = Range("A1:C7").Value

Dim b() As Variant
Let En = 4
Let b() = Application.Index(a(), Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))

' Or
Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))

End Sub``````

Last edited by Doc.AElstein on 22 Nov 2020, 12:05, edited 3 times in total.
\ -_- /
I have had my fill of hearing about death recently. I have decide to live for ever, or at least to die in the attempt YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

Thanks a lot but in fact I will use it for splitting a lot of arrays that will be preodcued
Generally, after testing I found that the second array is OK (3 rows) while the first array has (7 rows) and I expect (4 rows)

Code: Select all

``````    a = Range("A1").CurrentRegion.Value
r = ArrayOfArrays(a, 4)
Range("F1").Resize(UBound(r(0), 1), UBound(r(0), 2)).Value = r(0)
Range("F10").Resize(UBound(r(1), 1), UBound(r(1), 2)).Value = r(1)
`````` Doc.AElstein
BronzeLounger
Posts: 1227
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

### Re: Split 2D array into two equal arrays

Code: Select all

``````Sub first_output_array_would_be_4_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
Let a() = Range("A1:C7").Value

Dim c() As Variant
Let En = 4
Let c() = Application.Index(a(), Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))

' Or
Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))

End Sub``````

Code: Select all

``````Sub first_output_array_would_be_4_rows_and_three_columns__AND__second_output_array_would_be_3_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
Let a() = Range("A1:C7").Value

Dim c() As Variant, b() As Variant
Let En = 4

Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
End Sub``````

_._________________________________

YasserKhalil wrote:
30 Oct 2020, 11:53
... I will use it for splitting a lot of arrays that will
Maybe do it something like this

Code: Select all

``````Option Explicit
Dim c() As Variant, b() As Variant, a() As Variant
Sub first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(En As Long)   '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
End Sub
Sub Testit()
Let a() = Range("A1:C7").Value: Call first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(4)
' Now look what cha  got in c() and b()
End Sub``````

Loop that idea for your lot of arrays.....

Code: Select all

``````Option Explicit
Dim c() As Variant, b() As Variant, a() As Variant
Sub first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(En As Long)   '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "\$")(1) & ")"))
End Sub
Sub TestitLoopy()
Dim ARayOfARays() As Variant: Let ARayOfARays() = Array(Range("A1:C7").Value, Range("A1:C5").Value)
Dim StearIt As Variant
For Each StearIt In ARayOfARays()
Let a() = StearIt: Call first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(4)
' Now look what cha  got in c() and b()
Next StearIt
End Sub``````
Last edited by Doc.AElstein on 30 Oct 2020, 12:44, edited 1 time in total.
\ -_- /
I have had my fill of hearing about death recently. I have decide to live for ever, or at least to die in the attempt YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

Thanks a lot, Mr. Alan for this perfect solution.
In fact, what I am worried about is that the real data is too large and I am not sure if using the Application Index with evaluate will be able to deal with these large amounts. Doc.AElstein
BronzeLounger
Posts: 1227
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

### Re: Split 2D array into two equal arrays

I am not sure what the limits are.
Possibly row limit of about 65,000 with Index. ( The Evaluates are just getting indicia arrays, like {1, 2, 3, 4 ....} etc.... so that is less likely to be the limitation).
But it may all be influenced by your computer performance and many other things related to your Office version.

I don't have a lot of experience with large amounts of real data, so I am not sure of the real data limits...
\ -_- /
I have had my fill of hearing about death recently. I have decide to live for ever, or at least to die in the attempt YasserKhalil
PlatinumLounger
Posts: 4266
Joined: 31 Aug 2016, 09:02

### Re: Split 2D array into two equal arrays

Thanks a lot anyway for the nice solution you offered. I really liked it so much as it is very compact and can be used in other issues. I will certainly make use of it.
Best Regards