filling array with non-contiguous range

[koalabeer]

Hi everybody,

I have just started using VBA with excel and ran into the followin
problem:

If I want to fill an array with an excel column I can do:

array_in = Range("A1:A7").Value

this works fine. But now I want to create a two dimensional array wher
I want to fill each dimension with a different excel column (the tw
columns have an empty column in between them). So I tried:

array_in = Range("A1:A7,C1:C7").Value

however this didn't worked out. Of course I can do:

array_in = Range("A1:C7").Value, but then I create a three dimensiona
array with the second dimension empty. This will work but seems rathe
silly to me;-) Maybe I am overlooking something obvious but as a
excuse I am only a starter;-)

Can somebody help me?

Thanks,

Stev

 

[Tom Ogilvy]

To the best of my knowedge, you can't do what you ask unless you load the
array cell by cell

 

[Ken Johnson]

Hi Steve,

When you stated :
"Of course I can do:
array_in = Range("A1:C7").Value, but then I create a three dimensional
array with the second dimension empty."
It is still a two dimensional array, one dimension for the rows,
another for the columns. Each element in array_in only needs two
reference numbers to identify its position in the array, a row
reference and a column reference and you are stuck with the blank
column array_in(1 to 7,2).
Stick with using arrays, they are much, much faster than processing the
equivalent Range object. You just have to write your code so that the
blank column is skipped

Eg If every second column is blank and using a For Next loop...

For iRow = 1 to Ubound(array_in , 1)
For iColumn = 1 to Ubound(array_in,2) Step 2
Do Something
Next iColumn
Next iRow

Ken Johnson

 

[Leith Ross]

Hello Steve,

Here is a code routine to load the ranges you specify into a 2-D array.
The code will pad the array with empty strings if the range length is
less than the "rows" or second dimension of the array, and will not
copy any "rows" beyond that limit.

The code prevemts any gaps by combining the individual ranges into a
single contiguous range. This range is loaded into the 2-D array using
loops, as Tom pointed out.

There are only 2 lines of code you need to modify to increase the
number of ranges loaded. These lines are in bold type. One is the
dimensions of Array_In and the other is the Variant Array called
Ranges.

I realize this code is probably more complex than what you are used to.
If you have any questions, you can email me (e-mail address removed).


Code:
--------------------
Sub FillArrayFromRanges()

Dim Array_In(1, 6)
Dim Element
Dim I As Long, J As Long, LastRow As Long
Dim Padding As Long
Dim RA As Range, Rng As Range
Dim Ranges, SubRng
Dim Temp()
Dim X As Long

Ranges = Array(Range("A2:A6"), Range("C1:C7"))

Set Rng = Ranges(0)
For Each SubRng In Ranges
Set Rng = Application.Union(Rng, SubRng)
Next SubRng

For I = 0 To Rng.Areas.Count - 1
Set RA = Ranges(I)
LastRow = RA.Rows.Count
If LastRow > UBound(Array_In, 2) + 1 Then LastRow = UBound(Array_In, 2) + 1
Padding = UBound(Array_In, 2) - LastRow + 1
For J = 1 To LastRow
ReDim Preserve Temp(X)
Temp(X) = RA.Cells(J, 1).Value
X = X + 1
Next J
If Padding > 0 Then
For J = 1 To Padding
ReDim Preserve Temp(X)
Temp(X) = ""
X = X + 1
Next J
End If
Next I

X = 0
For I = 0 To UBound(Array_In, 1)
For J = 0 To UBound(Array_In, 2)
Array_In(I, J) = Temp(X)
X = X + 1
Next J
Next I

End Sub