2

I was attempting to use the union property to join a range of cells and take their values all at once, but after going through the code I noticed that myarray only retains the first ranges values( i.e C2:C12).

Sub macro()
    Dim myarray() As Variant
    Dim myrange As Range

    With Worksheets(1)
        Set myrange = Application.Union(.Range("C2:C12"), .Range("G2:G12"), _
        .Range("J2:J12"), .Range("T2:T12"))
    End With

    myarray = myrange.Value

End Sub

When I set the values to a range in a worksheet it works just fine, which leads me to believe it can access the values as an array. Is there a reason why this isn't working as I intended? Is there a better way to do this sort of operation?

Jchang43
  • 891
  • 6
  • 14
  • 2
    You can't use non-contiguous ranges to write to an array in this way. – QHarr Sep 19 '18 at 19:17
  • 2
    This is a surprisingly similar question to [this question asked earlier today](https://stackoverflow.com/q/52408217/9245853) - even the column letters are the same, although the row numbers are different. – BigBen Sep 19 '18 at 19:18
  • 1
    Looks like you'll have to loop on the areas of the union and just redim the array to include the new data with something like `For each in myrange.Areas` – Marcucciboy2 Sep 19 '18 at 19:18
  • @BigBen is this a conspiracy?? *xfiles music plays* – Marcucciboy2 Sep 19 '18 at 19:20
  • @BigBen I was actually trying to come up with my own solution to that problem, but I ran into this when I was trying to solve it. I was wondering if there was an explanation as to why I couldn't do this, but it seems like I just can't. Thanks for the help everyone, I'll refer to the other question for an answer. – Jchang43 Sep 19 '18 at 19:48
  • 1
    That explains a lot - I was sure it couldn't be a coincidence. That said, the answer there is a good approach. – BigBen Sep 19 '18 at 19:50

0 Answers0