union range into array

[Lavina]

Hey guys,

I'm trying to dodge making cycles to fill up my array, so i was hoping to just do this:

lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set bigRange = Application.Union(Range(Cells(2, 3), Cells(lastRow, 3)), Range(Cells(2, 4), Cells(lastRow, 4)), Range(Cells(2, 6), Cells(lastRow, 6)))
bigRange.Select
allDisposalsArray() = bigRange

with the select i can see that the range is definitely set correct and picks up all the data that i want, but its selecting columns 3,4,6 and when array is made i end up with only columns 3 and 4 in it.
What am i doing wrong?

 

[Norie]

You can't directly populate an array with all the data from non-contiguous ranges.

Filling an array by looping should be straightforward though, you could try something like this.

Function FillArray(rng As Range) As Variant
Dim rngArea As Range
Dim ColsCnt As Long
Dim RowsCnt As Long
Dim arrIn As Variant
Dim arrOut() As Variant
Dim I As Long
Dim J As Long

    RowsCnt = rng.Rows.Count
    
    ColsCnt = 1
    
    For Each rngArea In rng.Areas
        
        arrIn = rngArea.Value
            
        For J = LBound(arrIn, 2) To UBound(arrIn, 2)
            ReDim Preserve arrOut(1 To RowsCnt, 1 To ColsCnt)
                For I = LBound(arrIn, 1) To UBound(arrIn, 1)
                    arrOut(I, ColsCnt) = arrIn(I, J)
                Next I
            ColsCnt = ColsCnt + 1
        Next J
        
    Next rngArea
    
    FillArray = arrOut
End Function

Sub Test()
Dim bigRange As Range
Dim LastRow As Long
Dim allDisposalsArray As Variant

    LastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    Set bigRange = Application.Union(Range(Cells(2, 3), Cells(LastRow, 3)), Range(Cells(2, 4), Cells(LastRow, 4)), Range(Cells(2, 6), Cells(LastRow, 6)))

    allDisposalsArray = FillArray(bigRange)

    Range("I1").Resize(UBound(allDisposalsArray, 1), UBound(allDisposalsArray, 2)).Value = allDisposalsArray

End Sub

 

[Fluff]

How about

Sub Lavina()
   Dim Ary As Variant
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
   Ary = Application.Index(Range("A2:F" & UsdRws).Value, Evaluate("row(1:" & UsdRws & ")"), Array(3, 4, 6))
End Sub