Can't join ranges into array in VBA using Union

Question

I have two named ranges I want to join, ie append the 2nd range onto the end of the first one in an array. When I use Union I only get the first range in the array. If I just use Range it works but I can't join non-contiguous ranges.

Sub GetAbilities()
Dim Arr() As Variant   
Dim rng1 As Range
Dim rng2 As Range
Dim newRng As Range

Set rng1 = tbl.ListColumns("Ability1").DataBodyRange
Set rng2 = tbl.ListColumns("Ability2").DataBodyRange
Set newRng = Union(rng1, rng2)

'   Set newRng = Range(rng1, rng2)
'   This works fine

Arr = newRng

Dim Destination As Range
Set Destination = Sheets("test").Range("A1")
Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

End Sub

You will need to add each newRng.Area separately to the array. You cannot load a disjointed range into an array in one step. — Scott Craner
– Scott Craner, Commented Mar 3, 2018 at 19:13

QHarr · Accepted Answer · 2018-03-04 06:38:36Z

1

You are just stacking two columns on top of each other I think so you can loop as follows:

Option Explicit

Sub Test()

    Dim Arr() As Variant
    Dim tbl As ListObject

    Set tbl = ThisWorkbook.Worksheets("Sheet4").ListObjects("Table1") 'this would be set as per your requirements
    Dim totalOutputRows As Long
    Dim totalColumnRows As Long

    totalColumnRows = tbl.DataBodyRange.Rows.Count
    totalOutputRows = totalColumnRows * 2

    ReDim Arr(1 To totalOutputRows)
    Dim i As Long
    Dim j As Long

    For i = 1 To totalOutputRows

        If i <= totalColumnRows Then

            Arr(i) = tbl.ListColumns("Ability1").DataBodyRange(i, 1)

        Else

            j = j + 1
            Arr(i) = tbl.ListColumns("Ability2").DataBodyRange(j, 1)

        End If

    Next i

End Sub

You could also get rid of j and just put

Arr(i) = tbl.ListColumns("Ability2").DataBodyRange(i - totalColumnRows, 1)

edited Mar 4, 2018 at 6:38

answered Mar 3, 2018 at 21:10

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bigbucky Over a year ago

thanks I was hoping to avoid a loop but looks like that would do it

Ryan Wildry Over a year ago

You can do without looping, but it is probably a little inefficient for larger data sets. E.g. array3 = Split(Join(array1, ",") & "," & Join(array2, ","), ",")

Collectives™ on Stack Overflow

Can't join ranges into array in VBA using Union

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related