How to get Min / Max Row / Col in a non-contiguous range?

If you want the highest row number, loop through the Areas in the selection and check the row number of the last cell in each one, storing the max value.

To extend Rory's answer a bit: try the following code in an empty sheet:

Sub test()
Dim rng As Range, rarea As Range
Dim maxrow As Long, maxcol As Long, minrow As Long, mincol As Long
'Set rng = Range("A1:C10")
minrow = ActiveSheet.Rows.Count
mincol = ActiveSheet.Columns.Count
Set rng = Range("B3:E10,D5:G12,C5:D14,E3:F6")
For Each rarea In rng.Areas
  rarea.Select
  maxrow = WorksheetFunction.Max(maxrow, rarea.Row + rarea.Rows.Count - 1)
  maxcol = WorksheetFunction.Max(maxcol, rarea.Column + rarea.Columns.Count - 1)
  minrow = WorksheetFunction.Min(minrow, rarea.Row)
  mincol = WorksheetFunction.Min(mincol, rarea.Column)
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = rarea.Address(False, False)
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "rows: " & rarea.Row & ":" & rarea.Row + rarea.Rows.Count - 1
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "columns: " & rarea.Column & ":" & rarea.Column + rarea.Columns.Count - 1
  DoEvents
  Application.Wait Now + TimeValue("0:00:01")
Next rarea
MsgBox "rows: " & minrow & ":" & maxrow & vbCrLf & "columns: " & mincol & ":" & maxcol
End Sub

And now you can drop all the displaying-related parts of the code and use it for your somerange

Hi Marc,

I think that Range.Find will look on whole (potentially not continous) range and locate specihic values (What:=) so I'm not sure how it could be used to find "smalles surrounding rectangle" address - I think this is what superlative looks for.

PS. My code from above with removed side/demonstration parts seems to be still competitive compared to range.address and address splitting method:

Sub test()
Dim rng As Range, rarea As Range
Dim maxrow As Long, maxcol As Long, minrow As Long, mincol As Long
minrow = ActiveSheet.Rows.Count
mincol = ActiveSheet.Columns.Count
Set rng = Range("B3:E10,D5:G12,C5:D14,E3:F6")
For Each rarea In rng.Areas
  maxrow = WorksheetFunction.Max(maxrow, rarea.Row + rarea.Rows.Count - 1)
  maxcol = WorksheetFunction.Max(maxcol, rarea.Column + rarea.Columns.Count - 1)
  minrow = WorksheetFunction.Min(minrow, rarea.Row)
  mincol = WorksheetFunction.Min(mincol, rarea.Column)
Next rarea
MsgBox "rows: " & minrow & ":" & maxrow & vbCrLf & "columns: " & mincol & ":" & maxcol
End Sub

Hi everyone,

thanks for all the great ideas!! I actually managed to find some code online very similar to kaper's and modified it according to my needs. Here it is:

Sub Range_MinMax(sRange As Range, ByRef MinRow As Long, ByRef MaxRow As Long, ByRef MinCol As Long, ByRef MaxCol As Long)
    Dim c As Range, TempInt As Long, TempInt2 As Long, MinRange As Range, MaxRange As Range
    Dim subRange As Range, areaMaxRow As Long, areaMinRow As Long, areaMaxCol As Long, areaMinCol As Long, AreaCount As Long
    
    
    MinRow = 32000
    MaxRow = 1
    MinCol = 32000
    MaxCol = 1
    AreaCount = 0
    
    For Each subRange In sRange.Areas
        AreaCount = AreaCount + 1
        areaMaxRow = subRange.Rows(subRange.Rows.Count).Row
        areaMinRow = subRange.Rows(1).Row
        areaMaxCol = subRange.Rows(subRange.Columns.Count).Column
        areaMinCol = subRange.Columns(1).Column
        If areaMaxRow > MaxRow Then MaxRow = areaMaxRow
        If areaMinRow < MinRow Then MinRow = areaMinRow
        If areaMaxCol > MaxCol Then MaxCol = areaMaxCol
        If areaMinCol < MinCol Then MinCol = areaMinCol
    Next subRange
    
    If AreaCount = 1 Then
        Contiguous = True
    Else
        Contiguous = False
    End If

End Sub

using WorksheetFunction.Min appears cleaner, but is there a performance benefit? Anyone know? Thanks!

Originally Posted by superlative

using WorksheetFunction.Min appears cleaner, but is there a performance benefit? Anyone know? Thanks!

I would expect a call to Worksheetfunction to be slower, but you should test it.

I forgot something, using range address has a limit, the string address can't be longer than 255 characters, otherwise it will raise an error.
So I think your code or Kaper's code is better.

But actually how do you get the range? By selecting the range manually?