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.