excel vba how to copy the value of multiple non-contiguous ranges into an array

Question

I am trying to copy the value of multiple non-contiguous ranges into an array. I wrote code like this:

summaryTempArray = .range("A2:D9,A11:D12,A14:D15").Value

But it copies only the first part (A2:D9). Then, I tried the following and I get the error - "Method Union of Object _Global Failed" - is there any mistake in the way that I am using union?

summaryTempArray = Union(.range("A2:D9"), .range("A11:D12"), .range("A14:D15")).Value

Answer 1

Don't know what was wrong with your union, but it would have created the same range, which you stated in your first attempt.

The problem is, you have now multiple areas. Which you can, and as far as I know, has to address now.

Here is an example, which will resolve in an array of all areas, without adding each cell individually, but adding each area individually to the summary array:

Public Sub demo()
  Dim summaryTempArray() As Variant
  Dim i As Long

  With Tabelle1
    ReDim summaryTempArray(1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count)

    For i = 1 To .Range("A2:D9,A11:D12,A14:D15").Areas.Count
      summaryTempArray(i) = .Range("A2:D9,A11:D12,A14:D15").Areas(i)
    Next i
  End With

End Sub

Hope this helps.

Answer 2

I believe Jook's solution is as good as you are going to get if it is important to get the source ranges into an array. However, I think the solution should include instructions on extracting values from a ragged array. This is not difficult but the syntax is obscure.

I cannot get your Union statement to fail either. I assume there is something about the context that causes the failure which I cannot duplicate.

The code below shows that the two ranges are the same and that only the first sub-range is loaded to an array as you reported. It finishes with an alternative approach that might be satisfactory.

Option Explicit
Sub Test()

  Dim CellValue() As Variant
  Dim rng As Range

  With Worksheets("Sheet1")

    Set rng = .Range("A2:D9,A11:D12,A14:D15")
    Debug.Print rng.Address
    Set rng = Union(.Range("A2:D9"), .Range("A11:D12"), .Range("A14:D15"))
    Debug.Print rng.Address
    ' The above debug statements show the two ranges are the same.

    Debug.Print "Row count " & rng.Rows.Count
    Debug.Print "Col count " & rng.Columns.Count
    ' These debug statements show that only the first sub-range is included the
    ' range counts.

    CellValue = rng.Value

    Debug.Print "Rows " & LBound(CellValue, 1) & " to " & UBound(CellValue, 1)
    Debug.Print "Cols " & LBound(CellValue, 2) & " to " & UBound(CellValue, 2)
    ' As you reported only the first range is copied to the array.

    rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
    ' This shows you can copy the selected sub-ranges.  If you can copy the
    ' required data straight to the desired destination, this might be a
    ' solution.

  End With

End Sub