Efficient Way to get Bounds of a Range

[ttt123]

Hi I would like to get the bounds of a range in an efficient manner and was wondering if anyone knew of some properties or functions that could do this. i have included a sample function of what I would like to accomplish, except I don't want to loop through the elements as the range could be quite large.

Sub test()
Dim rng As Range, cell As Range, minCol As Long, minRow As Long, maxRow As Long, maxCol As Long
Set rng = Union(Range("G10"), Range("I11"), Range("F20"), Range("K24"))

minCol = Columns.Count
minRow = Rows.Count
maxRow = 0
maxCol = 0

For Each cell In rng
minCol = Application.WorksheetFunction.Min(minCol, cell.Column)
minRow = Application.WorksheetFunction.Min(minRow, cell.Row)
maxCol = Application.WorksheetFunction.Max(maxCol, cell.Column)
maxRow = Application.WorksheetFunction.Max(maxRow, cell.Row)
Next cell

Dim rngRectangular As Range
Set rngRectangular = Range(Cells(minRow, minCol), Cells(maxRow, maxCol))

End Sub

thanks,
Taylour

 

[shg]

Found this in my attic:

Function RangeBox(r As Range) As Range
    ' Returns a single range bounding the cells in r
    Dim iRowBeg     As Long
    Dim iRowEnd     As Long
    Dim iColBeg     As Long
    Dim iColEnd     As Long
    Dim iRow        As Long
    Dim iCol        As Long
    Dim nRow        As Long
    Dim nCol        As Long
    Dim rArea       As Range
    Dim wksDad      As Worksheet
 
    With r.Worksheet
        iRowBeg = .Columns.Count
        iColBeg = .Rows.Count
 
        If r.Areas.Count = 1 Then
            Set RangeBox = r

        Else
            For Each rArea In r.Areas
                iRow = rArea.Row
                iCol = rArea.Column
                nRow = rArea.Rows.Count
                nCol = rArea.Columns.Count
                If iRow < iRowBeg Then iRowBeg = iRow
                If iCol < iColBeg Then iColBeg = iCol
                If iRow + nRow - 1 > iRowEnd Then iRowEnd = iRow + nRow - 1
                If iCol + nCol - 1 > iColEnd Then iColEnd = iCol + nCol - 1
            Next rArea
 
            Set RangeBox = Range(.Cells(iRowBeg, iColBeg), .Cells(iRowEnd, iColEnd))
        End If
    End With
End Function

E.g.,

Sub x()
    With Range("G10, I11, F20, K24")
        .Interior.ColorIndex = 35
        RangeBox(.Cells).Select
    End With
End Sub

 

[pgc01]

Hi Taylour

Following shg's idea, here's another option:

Function RangeBox(r As Range)
Dim j As Long
 
Set RangeBox = r.Areas(1)
For j = 2 To r.Areas.Count
    Set RangeBox = Range(RangeBox, r.Areas(j))
Next j
End Function

For ex., test with

Sub Test()
Dim r As Range
 
Set r = Union(Range("B5"), Range("A3:F3"), Range("C10:C12"), Range("G4"))
MsgBox RangeBox(r).Address
End Sub

 

[ttt123]

thanks for your responses. I wanted to get a method without looping through. I coded this up, which should get the bounding range relatively efficiently:

Public Function GetBoundingRange(rng As Range) As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set ws = Worksheets.Add
    With ws
        .Range(rng.AddressLocal).Value = 1
        GetBoundingRange = .UsedRange.AddressLocal
    End With
    ws.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Function

Sub test()
    Dim rng As Range
    Set rng = Union(Range("G10"), Range("I11"), Range("F20"), Range("K24"))
    Debug.Print GetBoundingRange(rng)
End Sub

 

[shg]

pgc, that's one of few examples of recursion I've seen that I considered worthwhile -- it's lovely.

I shall ask that this thread be deleted and claim it as my own

 

[pgc01]

Hi Taylour

Yes, that should do it, it's similar to the solution we usually use to subtract ranges. The usual is to add a workbook instead of a worksheet to account for the case of a protected workbook, but that's maybe not a problem in your case.

The creation of a new worksheet/workbook must a heavy process compared to just composing the ranges. It would be interesting if you'd run some performance tests.

 

[pgc01]

pgc, that's one of few examples of recursion I've seen that I considered worthwhile -- that's lovely.

Thanks, I thought that your idea of enclosing the multi-area range in a box was great and then I remebered that I could join the ranges.

I shall ask that this thread be deleted and claim it as my own

Since the idea was yours, you can, at least, claim 50%.

 

[shg]

Misread it -- it's not recursive, but it's still compact and lovely.