filling array with non-contiguous range

**koalabeer** · 12-26-2005, 02:29 PM

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 · 12-26-2005, 04:35 PM

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 · 12-26-2005, 06:45 PM

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** · 12-27-2005, 08:41 PM

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.

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

Sincerely,
Leith Ross