Union(range) value fails vba

Question

I'm trying to set an array to the values of non consecutive arrays.

My code

With WBInfoProduit.Sheets(1)
Set myrange = Union(ActiveSheet.Range(Cells(1, ProdNameCol), Cells(rows, ProdNameCol)), ActiveSheet.Range(Cells(1, FundNameCol), Cells(rows, FundNameCol)), ActiveSheet.Range(Cells(1, CompagnieCol), Cells(rows, CompagnieCol)))

End With So the array is supossed to have 3 columns , but it only has 2 when I check with the debbuger.

Thank you guys.

Could you post more of your code? It's hard to see what exactly is happening without it. — KFichter, Aug 05 '15 at 17:18

score 1 · Accepted Answer · edited May 23 '17 at 11:51

A couple of things. Your code

    With WBInfoProduit.Sheets(1)
        Set myrange = Union( _
                  ActiveSheet.Range(Cells(1, ProdNameCol), Cells(Rows, ProdNameCol)) _
                , ActiveSheet.Range(Cells(1, FundNameCol), Cells(Rows, FundNameCol)) _
                , ActiveSheet.Range(Cells(1, CompagnieCol), Cells(Rows, CompagnieCol)) _
                )
    End With

Be aware that every "Cells" is referring to the cells on the activesheet. So

With WBInfoProduit.Sheets(1)

does nothing!

I tried this:

Sub xxx()


    Dim myrange As Range

    With ActiveSheet
        Set myrange = Union( _
                  .Range(.Cells(1,  5), .Cells(100,  5)) _
                , .Range(.Cells(1, 10), .Cells(100, 10)) _
                , .Range(.Cells(1, 20), .Cells(100, 20)) _
                )
    End With

    Debug.Print myrange.Address

End Sub

It resulted in this output

 $E$1:$E$100,$J$1:$J$100,$T$1:$T$100

So as long as ProdNameCol, FundNameCol, CompagnieCol in your code are set to different values it should work but possibly only on the active sheet.

HOWEVER,

If you are trying to assign ranges to an array you need to be aware of range areas!

See the code below which will help

Sub xxx()


    Dim myrange As Range
    Dim MyArray() As Variant

    Dim MyArray1() As Variant
    Dim MyArray2() As Variant
    Dim MyArray3() As Variant
    Dim MyArray4() As Variant

    With ActiveSheet
        Set myrange = Union( _
                  .Range(.Cells(1,  5), .Cells(100,  5)) _
                , .Range(.Cells(1, 10), .Cells(100, 10)) _
                , .Range(.Cells(1, 20), .Cells(100, 20)) _
                )
    End With


    Debug.Print myrange.Address
    Debug.Print myrange.Areas(1).Address
    Debug.Print myrange.Areas(2).Address
    Debug.Print myrange.Areas(3).Address

    MyArray = myrange
    MyArray1 = myrange.Areas(1)
    MyArray2 = myrange.Areas(2)
    MyArray3 = myrange.Areas(3)


    ' MyArray4 = Union(MyArray1, MyArray2, MyArray3)

End Sub

The MyArray4 line will nto work see here for help with this!

Union(range) value fails vba

1 Answers1