How to put data into an array?

[excelos]

Hello

I want to create an array with dynamic data (that is not a simple range), what is the best way to do that?

E.g. how can I create an array with A1:A5,A10:A15 ?

From my reading, it seems there are few ways, like creating a SEQUENCE first etc but what is the optimal and most flexible way?

Thanks!

 

[DanteAmor]

I don't know if it's the best, but you can copy the values to another cell and then you can take them as a sequence:

Sub testarr()
  Dim a As Variant
  Range("A1:A5, A10:A15").Copy
  Range("C1").PasteSpecial xlPasteValues
  a = Range("C1", Range("C" & Rows.Count).End(3)).Value
End Sub

 

[JoeMo]

Here's a way to put non-contiguous ranges into a vertical array and write the array to a column. The yellow-filled cells are the range myRng. myArr is written to col C.

Book1
	A	B	C	D	E	F	G	H	I	J
1	11		11			61	71	81	91	101
2	12		12			62	72	82	92	102
3	13		13
4	14		14
5	15		15
6			110
7			111
8			112
9			113
10	110		114
11	111		115
12	112		61
13	113		71
14	114		81
15	115		91
16			101
17			62
18			72
19			82
20			92
21			102
Sheet1

Cell Formulas
Range	Formula
F1:J2,A10:A15,A1:A5	F1	=COLUMN()&ROW()

Sub MakeArray()
'create an array from a multi-area range object
'and write the array to a (vertical)column
Dim myRng As Range, myArr As Variant, i As Long, Ar As Range, j As Long, ct As Long
Set myRng = Range("A1:A5,A10:A15,F1:J2")
ReDim myArr(1 To myRng.Count, 1 To 1)
For i = 1 To myRng.Areas.Count
    For j = 1 To myRng.Areas(i).Count
        ct = ct + 1
        myArr(ct, 1) = myRng.Areas(i).Cells(j).Value
    Next j
Next i
Range("C1:C" & UBound(myArr, 1)).Value = myArr
End Sub

 

[DanteAmor]

Hi @JoeMo, I was looking for a way to not read cell by cell, because that would make the process very slow, depending on the number of cells, of course. But, either way, it could be simplified into a single loop:

Sub testarr_3()
  Dim rng As Range, c As Range, a As Variant, n As Long
  Set rng = Range("A1:A5, A10:A15, F1:J2")
  ReDim a(1 To rng.Count, 1 To 1)
  
  For Each c In rng
    n = n + 1
    a(n, 1) = c.Value
  Next
  Range("C1").Resize(UBound(a)).Value = a
End Sub

 

[JoeMo]

Hi @JoeMo, I was looking for a way to not read cell by cell, because that would make the process very slow, depending on the number of cells, of course. But, either way, it could be simplified into a single loop:

Sub testarr_3()
  Dim rng As Range, c As Range, a As Variant, n As Long
  Set rng = Range("A1:A5, A10:A15, F1:J2")
  ReDim a(1 To rng.Count, 1 To 1)
 
  For Each c In rng
    n = n + 1
    a(n, 1) = c.Value
  Next
  Range("C1").Resize(UBound(a)).Value = a
End Sub

Good point Dante. I was trying to emphasize the multi-area-range aspect the OP seemed concerned about.

 

[Peter_SSs]

It depends on what sort of data you have in the range (text, numerical etc) and what you want to subsequently do with the array values but if your range of interest is going to consist of two 'areas' you can get the values (as strings) into an array without looping like this*.

Assumes "|" is not part of the data

Dim a As Variant
Dim myRng As Range

Set myRng = Range("A1:A5,A10:A15")
a = Split(Application.TextJoin("|", 0, myRng.Areas(1), myRng.Areas(2)), "|")

If the number of areas is unknown then this at least only requires looping each area, not each cell.

Dim a As Variant
Dim myRng As Range, rA As Range
Dim s As String

Set myRng = Range("A1:A5,A10:A15")
For Each rA In myRng.Areas
  s = s & "|" & Application.TextJoin("|", 0, rA.Value)
Next rA
a = Split(Mid(s, 2), "|")

* Caveat with both codes is that the ranges and/or the values in them are not so big as to exceed the TEXTJOIN character limit of 32,767

 

[excelos]

Sorry forgot to mention, no VBA please. Any formula way? @JoeMo thanks will dive into that!

 

[Peter_SSs]

Sorry forgot to mention, no VBA please. Any formula way?

In that case, what exactly do you mean by "put into an array"?
Tell (&/or show with XL2BB) us more about exactly what you have and what you are trying to end up with.

 

[Peter_SSs]

Taking another guess:

excelos_1.xlsm
	A	B	C
1	a		a
2	b		b
3	c		c
4	d		d
5	e		e
6	f		j
7	g		k
8	h		l
9	i		m
10	j		n
11	k		o
12	l
13	m
14	n
15	o
Sheet1

Cell Formulas
Range	Formula
C1:C11	C1	=FILTER(A1:A15,(ROW(A1:A15)<=5)+(ROW(A1:A15)>=10))
Dynamic array formulas.

 

[bebo021999]

This collect A1:A5 and A11:A15 into a 2D array
=CHOOSE({1,2},A1:A5,A11:A15)
Then, for example, SUM:
=SUM(CHOOSE({1,2},A1:A5,A11:A15))

Book1
	A	B	C	D	E	F
1	1			#VALUE!		{1,7;2,8;3,9;4,10;5,11}
2	2			60
3	3
4	4
5	5
6
7
8
9
10
11	7
12	8
13	9
14	10
15	11
Sheet1

Cell Formulas
Range	Formula
D1	D1	=CHOOSE({1,2},A1:A5,A11:A15)
D2	D2	=SUM(CHOOSE({1,2},A1:A5,A11:A15))