How to merge multiple ranges into one array.

[JOAO12]

Hy everyone,

The below code works when the ranges are A5:J39, A44:J65 and A70:J89

Sub RangesToArray()
  Dim vRws As Variant, vData As Variant
  
  vRws = Application.Transpose(Split( _
          Join(Application.Transpose(Evaluate("row(5:39)"))) & " " & _
          Join(Application.Transpose(Evaluate("row(44:65)"))) & " " & _
          Join(Application.Transpose(Evaluate("row(70:89)")))))
  vData = Application.Index(Cells, vRws, Application.Transpose(Evaluate("row(1:10)")))
End Sub

What should be changed so that it works with the ranges B3:F13, I3:M20 and P30:T49 ? The final array (vData) should have 49 rows (11 + 18 + 20) by 5 columns.

Thanks.

 

[StephenCrump]

This approach doesn't generalise well, without a lot of contrivance.

You're probably better off just combining the arrays in VBA. Perhaps something like:

Sub Test()

    Dim v As Variant
    
    v = RangesToArray(Range("B3:F13"), Range("I3:M20"), Range("P30:T49"))
    Range("V1").Resize(UBound(v), UBound(v, 2)).Value = v
        
    'Or with Excel 365, a simple:
    Range("V1").Formula2 = "= RangesToArray(B3:F13,I3:M20,P30:T49)"
        
End Sub
Function RangesToArray(ParamArray rng() As Variant) As Variant

    Dim i As Long, r As Long, c As Long, NoRows As Long, NoCols As Long, Count As Long
    Dim InArr() As Variant, OutArr() As Variant
    ReDim InArr(LBound(rng) To UBound(rng))

    For i = LBound(rng) To UBound(rng)
        InArr(i) = rng(i).Value
        NoRows = NoRows + UBound(InArr(i))
        NoCols = Application.Max(NoCols, UBound(InArr(i), 2))
    Next i
    ReDim OutArr(1 To NoRows, 1 To NoCols)

    For i = LBound(InArr) To UBound(InArr)
        For r = 1 To UBound(InArr(i))
            For c = 1 To UBound(InArr(i), 2)
                OutArr(r + Count, c) = InArr(i)(r, c)
            Next c
        Next r
        Count = Count + r - 1
    Next i

    RangesToArray = OutArr

End Function

 

[JOAO12]

Stephen,
Thank you very much. Your code solved this problem.

 

[Peter_SSs]

What Excel version are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also wondering what you are going to do with the new single array? Is it going back onto the worksheet?

 

[Peter_SSs]

The reason that I was asking about version is that IF you have Microsoft 365 and happen to have the latest functions that are gradually being rolled out to subscribers, you can do this very easily.

Here is a smaller example where my original ranges are the green ones and the code below has produced the blue one.

And if you happen to be doing this in vba because it is difficult with formulas, with the new VSTACK function it just requires a single formula in a single cell (yellow) to produce the same results without vba.

22 06 09.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3	A 1 - 65	A 1 - 5	A 1 - 41	A 1 - 60	A 1 - 92			A 2 - 7	A 2 - 49	A 2 - 81	A 2 - 62	A 2 - 93
4	A 1 - 85	A 1 - 5	A 1 - 34	A 1 - 53	A 1 - 78			A 2 - 81	A 2 - 68	A 2 - 96	A 2 - 7	A 2 - 19
5	A 1 - 78	A 1 - 65	A 1 - 7	A 1 - 42	A 1 - 22
6	A 1 - 84	A 1 - 57	A 1 - 37	A 1 - 62	A 1 - 99										A 3 - 7	A 3 - 6	A 3 - 16	A 3 - 56	A 3 - 82
7															A 3 - 87	A 3 - 20	A 3 - 56	A 3 - 5	A 3 - 51
8															A 3 - 20	A 3 - 95	A 3 - 32	A 3 - 84	A 3 - 16
9															A 3 - 81	A 3 - 20	A 3 - 9	A 3 - 95	A 3 - 37
10	A 1 - 65	A 1 - 5	A 1 - 41	A 1 - 60	A 1 - 92			A 1 - 65	A 1 - 5	A 1 - 41	A 1 - 60	A 1 - 92			A 3 - 12	A 3 - 9	A 3 - 80	A 3 - 76	A 3 - 52
11	A 1 - 85	A 1 - 5	A 1 - 34	A 1 - 53	A 1 - 78			A 1 - 85	A 1 - 5	A 1 - 34	A 1 - 53	A 1 - 78			A 3 - 6	A 3 - 70	A 3 - 56	A 3 - 31	A 3 - 74
12	A 1 - 78	A 1 - 65	A 1 - 7	A 1 - 42	A 1 - 22			A 1 - 78	A 1 - 65	A 1 - 7	A 1 - 42	A 1 - 22			A 3 - 43	A 3 - 35	A 3 - 21	A 3 - 67	A 3 - 21
13	A 1 - 84	A 1 - 57	A 1 - 37	A 1 - 62	A 1 - 99			A 1 - 84	A 1 - 57	A 1 - 37	A 1 - 62	A 1 - 99			A 3 - 78	A 3 - 66	A 3 - 34	A 3 - 61	A 3 - 60
14	A 2 - 7	A 2 - 49	A 2 - 81	A 2 - 62	A 2 - 93			A 2 - 7	A 2 - 49	A 2 - 81	A 2 - 62	A 2 - 93			A 3 - 81	A 3 - 96	A 3 - 42	A 3 - 42	A 3 - 1
15	A 2 - 81	A 2 - 68	A 2 - 96	A 2 - 7	A 2 - 19			A 2 - 81	A 2 - 68	A 2 - 96	A 2 - 7	A 2 - 19			A 3 - 5	A 3 - 61	A 3 - 13	A 3 - 84	A 3 - 18
16	A 3 - 7	A 3 - 6	A 3 - 16	A 3 - 56	A 3 - 82			A 3 - 7	A 3 - 6	A 3 - 16	A 3 - 56	A 3 - 82
17	A 3 - 87	A 3 - 20	A 3 - 56	A 3 - 5	A 3 - 51			A 3 - 87	A 3 - 20	A 3 - 56	A 3 - 5	A 3 - 51
18	A 3 - 20	A 3 - 95	A 3 - 32	A 3 - 84	A 3 - 16			A 3 - 20	A 3 - 95	A 3 - 32	A 3 - 84	A 3 - 16
19	A 3 - 81	A 3 - 20	A 3 - 9	A 3 - 95	A 3 - 37			A 3 - 81	A 3 - 20	A 3 - 9	A 3 - 95	A 3 - 37
20	A 3 - 12	A 3 - 9	A 3 - 80	A 3 - 76	A 3 - 52			A 3 - 12	A 3 - 9	A 3 - 80	A 3 - 76	A 3 - 52
21	A 3 - 6	A 3 - 70	A 3 - 56	A 3 - 31	A 3 - 74			A 3 - 6	A 3 - 70	A 3 - 56	A 3 - 31	A 3 - 74
22	A 3 - 43	A 3 - 35	A 3 - 21	A 3 - 67	A 3 - 21			A 3 - 43	A 3 - 35	A 3 - 21	A 3 - 67	A 3 - 21
23	A 3 - 78	A 3 - 66	A 3 - 34	A 3 - 61	A 3 - 60			A 3 - 78	A 3 - 66	A 3 - 34	A 3 - 61	A 3 - 60
24	A 3 - 81	A 3 - 96	A 3 - 42	A 3 - 42	A 3 - 1			A 3 - 81	A 3 - 96	A 3 - 42	A 3 - 42	A 3 - 1
25	A 3 - 5	A 3 - 61	A 3 - 13	A 3 - 84	A 3 - 18			A 3 - 5	A 3 - 61	A 3 - 13	A 3 - 84	A 3 - 18
26
Sheet2 (2)

Cell Formulas
Range	Formula
I10:M25	I10	=VSTACK(B3:F6,I3:M4,P6:T15)
Dynamic array formulas.

Sub Test_VSTACK()
  Dim vData As Variant
 
  vData = Evaluate("vstack(B3:F6,I3:M4,P6:T15)")
  Range("B10").Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData
End Sub