none
How to join two ranges RRS feed

  • Question

  • Hi

    I am trying to wirte some code (see below)  to 'join' two ranges so that I can output them to an array once joined.

    In the example below rngA is 4 columns and 6 rows; rngB is 4 columns and 3 rows;

    What happens is Union statement seems to ignore the second range and only picks  rngA and ignores rngB;  if I switch the parameters and make rngB first the union command ignores rngA;

    What I am doing wrong and is there a better way to combine ranges to get them output to  an array? 

    many thanks

    Peter

    Dim rngA As Range
    Dim rngB As Range
    Dim vararray As Variant

    Set rngA = Sheet2.Range("A1").CurrentRegion
    Set rngB = Sheet2.Range("A10").CurrentRegion


    vararray = Union(rngA, rngB)

    Sunday, January 22, 2017 12:02 PM

Answers

  • Union(rngA, rngB) is a range consisting of two non-adjacent areas.

    An array cannot contain non-adjacent areas, so when you set an array to the value of Union(rngA, rngB), it picks the first area in the union.

    The same would happen if you used

    varArray = Range("A1:B2,A10:B11")

    The array would only contain the values of A1:B2.

    As a workaround you could copy Sheet2.Range("A1").CurrentRegion to another location, then copy Sheet2.Range("A10").CurrentRegion immediately below it. You now have a contiguous range that you can assign to an array:

        Dim rngA As Range
        Dim rngB As Range
        Dim vararray As Variant
    
        With Sheet2
            .Range("A1").CurrentRegion.Copy Destination:=.Range("Z1")
            Set rngA = .Range("Z1").CurrentRegion
            .Range("A10").CurrentRegion.Copy Destination:=.Range("Z1").Offset(rngA.Rows.Count)
            Set rngB = .Range("Z1").CurrentRegion
            vararray = rngB.Value
            ' Optional: clear the union range
            rngB.ClearContents
        End With
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 22, 2017 2:55 PM

All replies

  • Union(rngA, rngB) is a range consisting of two non-adjacent areas.

    An array cannot contain non-adjacent areas, so when you set an array to the value of Union(rngA, rngB), it picks the first area in the union.

    The same would happen if you used

    varArray = Range("A1:B2,A10:B11")

    The array would only contain the values of A1:B2.

    As a workaround you could copy Sheet2.Range("A1").CurrentRegion to another location, then copy Sheet2.Range("A10").CurrentRegion immediately below it. You now have a contiguous range that you can assign to an array:

        Dim rngA As Range
        Dim rngB As Range
        Dim vararray As Variant
    
        With Sheet2
            .Range("A1").CurrentRegion.Copy Destination:=.Range("Z1")
            Set rngA = .Range("Z1").CurrentRegion
            .Range("A10").CurrentRegion.Copy Destination:=.Range("Z1").Offset(rngA.Rows.Count)
            Set rngB = .Range("Z1").CurrentRegion
            vararray = rngB.Value
            ' Optional: clear the union range
            rngB.ClearContents
        End With
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 22, 2017 2:55 PM
  • thanks Hans!
    Wednesday, January 25, 2017 9:12 AM