noncontiguous range, minimal row...

[hurgadion]

Hi,
is it a simple Way to find a minimal row of a noncontiguous Range (number of Areas may be unknown... but we have function AREAS()) using a Formula ??

for (B4:B7,E2:E15,G6:G10,I3:I20) result is 2...

best regards,
hurgadion

 

[hurgadion]

Yes... The above formula returns minimal row of first Area... It's a little problem with function INDEX in array Formula approach, I think...

 

[Joe4]

Does that work for you? It returns 4 for me, rather than 2.

Same for me, it returned 4 instead of 2.

 

[RoryA]

In all my experiments, INDEX returns a #VALUE! error if you pass an array as the AREAS argument.

 

[hurgadion]

maybe this array construction may be useful ? I don't know...

=MIN(INDEX(A1:A10,N(IF(1,{1;3;5}))))

 

[Rick Rothstein]

Function FindMinRow(myRange As Range) As Long

    Dim cell As Range
    Dim minRow As Long
  
'   Originally set minRow to be last row on sheet
    minRow = Rows.Count
    
'   Loop through all ranges
    For Each cell In myRange
        If cell.Row < minRow Then minRow = cell.Row
    Next cell
    
    FindMinRow = minRow
    
End Function

It would be a little more efficient to iterate each area rather than each cell. Also, you can use the function name like a variable directly within the function instead of creating/using your minRow dummy variable.

Function GetMinRow(Rng As Range) As Long
  Dim Ar As Range
  GetMinRow = Rows.Count
  For Each Ar In Rng.Areas
    If Ar.Row < GetMinRow Then GetMinRow = Ar.Row
  Next
End Function

 

[RoryA]

You might also add a line to exit the loop if GetMinRow = 1.

 

[pgc01]

Hi

You can use the rectangular range that envelops them:

=MIN(ROW((B4:B7):(E2:E15):(G6:G10):(I3:I20)))

Remarks:
- for the Max() we need to CSE. Maybe we should use it also for the Min?
- the parentheses are not really needed.

 

[hurgadion]

OK,
thank You... is it possible to repeat the above result for a named Range using only this name or a function AREAS() ?

What is CSE ?

How from string "A1:A2;P1:P2" obtain a object A1:A2:P1:P2... ? a function INDIRECT not works, I think...

Best regards...

 

[Rick Rothstein]

You can use the rectangular range that envelops them:

=MIN(ROW((B4:B7):(E2:E15):(G6:G10):(I3:I20)))

Neat! I have never seen a set of three or more colon delimited chained ranges like that before. Not sure how to translate that to a non-contiguous Defined Name range in a formula, but it does make the UDF (user defined function) simpler, that is for sure...

Function GetMinRow(Rng As Range) As Long
  GetMinRow = Range(Replace(Rng.Address, ",", ":")).Row
End Function

 

[hurgadion]

Nice VBA Conclusion RR...