The intersection of two range arrays

Question

I currently have two arrays, each of which contains ranges. How would you like to get the intersection of these two arrays. In other words, I would like to get an array of ranges that contains only the ranges that are contained in both two source arrays. I tried .Intersect, but this does not work on arrays, as I found out.

array1: (Range ("A1"), Range ("B1"), Range ("C1")) array2: (Range ("A1"), Range ("A2"), Range ("A3"))

Result: (Range ("A1"))

Answer 1

you can use this code. The idea is to combine your array in one range using iterative Union. Then you can use the built-in Intersect.

Function IntersectArray(array1() As Range, array2() As Range) As Range
    Dim unionRangeArray1 As Range, unionRangeArray2 As Range
    Dim i As Integer

    Dim lbound1 As Integer: lbound1 = LBound(array1)
    Dim lbound2 As Integer: lbound2 = LBound(array2)

    Set unionRangeArray1 = array1(lbound1)
    Set unionRangeArray2 = array2(lbound2)

    For i = lbound1 + 1 To UBound(array1)
        Set unionRangeArray1 = Application.Union(unionRangeArray1, array1(i))
    Next

    For i = lbound2 + 1 To UBound(array2)
        Set unionRangeArray2 = Application.Union(unionRangeArray2, array2(i))
    Next

    Set IntersectArray = Application.Intersect(unionRangeArray1, unionRangeArray2)
End Function