filling array with non-contiguous range

[koalabeer]

Hi everybody,

I have just started using VBA with excel and ran into the following 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 where I want to fill each dimension with a different excel column (the two 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 dimensional array with the second dimension empty. This will work but seems rather silly to me;-) Maybe I am overlooking something obvious but as an excuse I am only a starter;-)

Can somebody help me?

Thanks,

Steve

[Tom Ogilvy]

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



--
Regards,
Tom Ogilvy

"koalabeer" <koalabeer.20ntry_1135621801.1773@excelforum-nospam.com> wrote
in message news:koalabeer.20ntry_1135621801.1773@excelforum-nospam.com...
>
> Hi everybody,
>
> I have just started using VBA with excel and ran into the following
> 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 where
> I want to fill each dimension with a different excel column (the two
> 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 dimensional
> array with the second dimension empty. This will work but seems rather
> silly to me;-) Maybe I am overlooking something obvious but as an
> excuse I am only a starter;-)
>
> Can somebody help me?
>
> Thanks,
>
> Steve
>
>
> --
> koalabeer
> ------------------------------------------------------------------------
> koalabeer's Profile:
http://www.excelforum.com/member.php...o&userid=29898
> View this thread: http://www.excelforum.com/showthread...hreadid=496056
>

[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 LeithRoss@aol.com.

Please Login or Register  to view this content.

Sincerely,
Leith Ross