excel - 获取多区域联合的外部边界范围

Question

高看低看，没发现有人讲过这个:
我在 VBA 中有 2 个或更多范围已“合并”(因此 rngUnion.Areas.Count >= 2 )并且区域范围是部分连续的(例如 rngUnion.Areas(1).address = "A1:Y75" 、 rngUnion.Areas(2).address = "A76:U123" 等)。
在 rngUnion 中获取组合区域的外部边界范围对象的简单/有效方法是什么？我有下面的代码可以做到这一点，但它看起来 super 笨拙和愚蠢 - 我相信有更好的方法。
注意:我假设这些区域周围可能有其他使用过的单元格不属于联合体，所以我非常犹豫是否要使用 .CurrentRegion , .UsedRange , 或 .End(xlUp).Row建议使用范围的类型方法。

Sub SomeObfuscatedMethodForGettingAUnionOfPartiallyContiguousAreas()
    Dim rng1 As Range: Set rng1 = Range("A1:Y75")
    Dim rng2 As Range: Set rng2 = Range("A76:U123")
    Dim rngUnion As Range, rngComplete As Range
    
    Set rngUnion = Union(rng1, rng2)
    
    Set rngComplete = GetOuterBoundingRange(rngUnion)
    Debug.Print rngComplete.Address 'prints "A1:Y123"
End Sub

Function GetOuterBoundingRange(rngUnion As Range) As Range
        Dim minRow As Long: minRow = 2147483647
        Dim minCol As Long: minCol = 2147483647
        Dim maxRow As Long: maxRow = 0
        Dim maxCol As Long: maxRow = 0
        Dim minRowTemp As Long
        Dim minColTemp As Long
        Dim maxRowTemp As Long
        Dim maxColTemp As Long
        Dim area As Range
        
        For Each area In rngUnion.Areas
            minRowTemp = area.Row
            maxRowTemp = minRowTemp + area.Rows.Count - 1
            minColTemp = area.Column
            maxColTemp = minColTemp + area.Columns.Count - 1
            
            If minRowTemp < minRow Then minRow = minRowTemp
            If minColTemp < minCol Then minCol = minColTemp
            If maxRowTemp > maxRow Then maxRow = maxRowTemp
            If maxColTemp > maxCol Then maxCol = maxColTemp
        Next area
        With rngUnion.parent        
            Set GetOuterBoundingRange = .Range(.Cells(minRow, minCol), .Cells(maxRow, maxCol))
        End With
End Function

Answer 1

据我所知，没有内置函数可以这样做。我不认为您的功能那么笨拙，在所有情况下，您都需要遍历所有区域并找到最小和最大行和列。
我的尝试是通过将数字收集到数组中并使用 Min 和 Max 函数来缩短一点时间，但基本上它是在做同样的事情。

Function getR(r As Range) As Range
    ReDim minRow(1 To r.Areas.Count) As Long
    ReDim maxRow(1 To r.Areas.Count) As Long
    ReDim minCol(1 To r.Areas.Count) As Long
    ReDim maxCol(1 To r.Areas.Count) As Long
    
    Dim i As Long
    For i = 1 To r.Areas.Count
        minRow(i) = r.Areas(i).Row
        maxRow(i) = r.Areas(i).Row + r.Areas(i).Rows.Count
        minCol(i) = r.Areas(i).Column
        maxCol(i) = r.Areas(i).Column + r.Areas(i).Columns.Count
    Next
    With r.Parent
        Set getR = .Range(.Cells(WorksheetFunction.Min(minRow), WorksheetFunction.Min(minCol)), _
                          .Cells(WorksheetFunction.Max(maxRow) - 1, WorksheetFunction.Max(maxCol) - 1))
    End With

End Function