两个范围数组的交集 [英] 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: (Range("A1"),Range("B1"),Range("C1"))数组2:(范围(A1"),范围(A2"),范围(A3"))
array1: (Range("A1"),Range("B1"),Range("C1")) array2: (Range("A1"),Range("A2"), Range("A3"))
结果:(范围(A1"))
Result: (Range("A1"))
推荐答案
你可以使用这个代码.这个想法是使用迭代 Union
将您的数组合并到一个范围内.然后就可以使用内置的Intersect
了.
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屋!