范围的两个数组的交集 [英] Intersection of two arrays of ranges

问题描述

我目前有两个数组每个包含范围。你会如何​​去获得这两个数组的交集。换句话说，我想获得范围的阵列，只包含中包含的两个两个原始阵列的范围。我试图.Intersect但我知道，不会对数组。

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：（范围（A1），范围（B1），范围（C））
数组2：（范围（A1），范围（A2），范围（A3））

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

结果：（范围（A1））

Result: (Range("A1"))

推荐答案

您可以使用此code。这个想法是使用迭代联盟合并阵列在一个范围内。然后你可以使用内置的相交。

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

这篇关于范围的两个数组的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！