# noncontiguous range, minimal row...

##### Active Member
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,

Last edited:

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### pbornemeier

##### Well-known Member
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.
Code:
``````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``````

##### Active Member
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.
Code:
``````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)

##### Active Member
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...

Last edited:

#### 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.

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

#### mikerickson

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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.

Replies
3
Views
152
Replies
2
Views
308
Replies
13
Views
2K
Replies
4
Views
264
Replies
3
Views
454

1,196,027
Messages
6,012,953
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back