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

 

[pbornemeier]

If you can add a helper column, you could do a MIN() for each row then use INDEX(MATCH()) to find the row of the min in that column.

 

[Joe4]

Here is some VBA code that will return the minimum row of your range.

Sub FindMinRow()

    Dim myRange As Range
    Dim cell As Range
    Dim minRow As Long
    
    Set myRange = Range("B4:B7,E2:E15,G6:G10,I3:I20")
    
'   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
    
    MsgBox "Minimum row in range is: " & minRow
    
End Sub

 

[hurgadion]

Thank You for quick Answer... I know VBA solution... I know a solution with helper column... I'm looking for a one Formula Solution... the best only using a name of the above Range...

 

[Joe4]

I do not know how you would do that solely with native Excel functions. I imagine it can probably be done, but I am not sure how.

If it were me, I would just create my own function to do that. It is just a slight variation of the VBA code I posted, changing it from a Sub Procedure to a Function, i.e.

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

Then, it would work like any other function in Excel, i.e.
=FindMinRow(MyRangeName)

 

[hurgadion]

OK,
thank You Joe4 very much... I need now a Formula Solution (hobby motivation)... I know function AREAS(name), it returns a number of areas... It may be useful function INDEX(name;;;nr_of_area)... and may be a function CELL("row", name)... I don't know how connect these Informations...

 

[Joe4]

Not sure what "hobby motivation" means...

In any event, I too would be interested in what a strictly formula approach would look like. Every web search I do shows VBA to do it. I will see if some of my fellow MVPs have any ideas.

 

[hurgadion]

hobby Motivation... It's not necessary practical... because a Solution exists, but we look for an another... more particular...

 

[mikerickson]

I do not know how you would do that solely with native Excel functions. I imagine it can probably be done, but I am not sure how.
....

Make a Named range of the discontinuous range in question, then the formula would be

=MIN(ROW(INDEX(myRange,0,0,ROW(A1:INDEX(A:A,AREAS(myRange),1)))))

 

[RoryA]

Make a Named range of the discontinuous range in question, then the formula would be

=MIN(ROW(INDEX(myRange,0,0,ROW(A1:INDEX(A:A,AREAS(myRange),1)))))

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