Is there a simple way to combing 1D arrays?

[Steve]

I need to be able to combine multipe arrays into another 1D array in order

Array1 = Range ("A1:A7")
Array2 = Range ("B1:B7")
Array3 = Range("C1:C7")
etc, etc, etc (it could get very large)

The result should be a 1D array. Assuming only the three arrays above,
LBOUND value should be A1.value. UBOUND value should be C7.value.

 

[Tom Ogilvy]

Using 3 ranges as an example:

Dim arr(), rng as range, cell as Range
Dim i as Long
set rng = Range("A1:A7,B1:B7,C1:C7")
redim arr(1 to rng.count)
i = 0
for each cell in rng
i = i + 1
arr(i) = cell.value
Next

..

Another way would be to copy the values to a single column on the worksheet,
pick them up from there and use transpose to make is 1D, however, this would
probably be slower.

 

[Steve]

Tom

Thank you. I made a few modifications to get around my bad question but, I
havve it working exactly as I need.

Steve

 

[Dana DeLouis]

Hi. Here's something a little different.
Note that the array v1 and v2 are slightly different in their dimensions.
I had some test data in A1:C3, and I included a number in the array. This
probably could be written a little better...

Sub Example()
Dim v1, v2, v3, v4
Dim MyArray As Variant
v1 = [A1:A3]
v2 = [Transpose(A1:A3)]
v3 = [B1:B3]
v4 = [C1:C3]
MyArray = Flatten(v1, v2, v3, 3.14, v4)
End Sub

Function Flatten(ParamArray m() As Variant)
Dim J As Long
Dim R As Long
Dim C As Long
Dim n As Long
Dim d As Variant

Set d = CreateObject("Scripting.Dictionary")
n = 1

For J = LBound(m) To UBound(m)
Select Case ArrayDepth(m(J))
Case 0
d.Add n, m(J)
n = n + 1
Case 1
For R = LBound(m(J)) To UBound(m(J))
d.Add n, m(J)(R)
n = n + 1
Next R
Case 2
For R = LBound(m(J), 1) To UBound(m(J), 1)
For C = LBound(m(J), 2) To UBound(m(J), 2)
d.Add n, m(J)(R, C)
n = n + 1
Next C
Next R
Case Else
'Not sure...
End Select
Next J
Flatten = d.items
End Function

Function ArrayDepth(v) As Long
Dim n As Long
Dim Dummy As Long

If Not IsArray(v) Then
ArrayDepth = 0
Exit Function
End If

On Error Resume Next
Do While Err.Number = 0
n = n + 1
Dummy = UBound(v, n)
Loop
ArrayDepth = n - 1
End Function