4

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

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

Result: (Range("A1"))

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
user2844947
  • 75
  • 1
  • 9
  • Can you give an example input/output? – Tmdean May 15 '14 at 16:35
  • OP I think you mean - based on interpretation of the question and your accepted answer - that you want a single Range that is the Range of Ranges that are contained within both of the original arrays. The current question mentions wanting an 'array of ranges' which I am not sure makes that much sense at presence (i.e. the result is a scalar `Range` not an array `Range()`). Are you perhaps able to amend you question for posterity? :) – Cor_Blimey May 15 '14 at 18:39

1 Answers1

5

you can use this code. The idea is to merge your array in a single range using an 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
Seb
  • 1,230
  • 11
  • 19
  • If you read my question, I have two arrays of ranges, not two ranges. – user2844947 May 15 '14 at 16:33
  • +1 nicely done but, just for anyone's future use, I would suggest you test for the lower bound because an array need not be base 1. Also, to save an unnecessary duplicative call to Union with the range that is already in the Union (e.g. at present when r is array1(1)) perhaps consider using a For i = x to y using Range.Areas(i) rather than For Each r in). – Cor_Blimey May 15 '14 at 18:42
  • 1
    Thank you for the hints @Cor_Blimey I will update my answer accordingly. – Seb May 16 '14 at 01:17
  • @Seb whoops - thank you for ignoring my silly point about Range.Areas - I must've had a few too many... ;) – Cor_Blimey May 16 '14 at 17:37