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

 

[Rick Rothstein]

Nice VBA Conclusion RR...

Thanks! It works in VBA because the argument to the Range object is a text string which is easily manipulated... unfortunately this does not seem translate over to the formula world (at least I haven't been able to crack it yet) because range references are not text strings there and when I tried to apply the INDIRECT function, it kept giving me a #REF! error, so I guess INDIRECT does not seem to like the multiple colon delimited ranges syntax.

 

[Marcelo Branco]

Not sure if when using
=CELL("address",MyRange)
in an English Excel-version you get a list separated by semi-colons or by commas

Assuming a list separated by semicolons like this
$B$4;$E$2:$E$15;$G$6:$G$10;$I$3:$I$20

Maybe this array formula (not elegant but seems it works)
=MIN(IFERROR(0+MID(TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(CELL("address",MyRange),":",";"),";",REPT(" ",100)),ROW(A1:A100)*100,100)),1+SEARCH("#",SUBSTITUTE(TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(CELL("address",MyRange),":",";"),";",REPT(" ",100)),ROW(A1:A100)*100,100)),"$","#",2)),100),""))
Ctrl+Shift+Enter

where MyRange is a named range --> $B$4:$B$7;$E$2:$E$15;$G$6:$G$10;$I$3:$I$20

M.

 

[GTO]

Hi Marcelo,

Here I get: $B$4,$E$2:$E$15,$G$6:$G$10,$I$3:$I$20

Mark

 

[Marcelo Branco]

Hi Marcelo,

Here I get: $B$4,$E$2:$E$15,$G$6:$G$10,$I$3:$I$20

Mark

Mark
Thank you

So, i hope this works for you

=MIN(IFERROR(0+MID(TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(CELL("address",MyRange),":",","),",",REPT(" ",100)),ROW(A1:A100)*100,100)),1+SEARCH("#",SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(CELL("address",MyRange),":",","),",",REPT(" ",100)),ROW(A1:A100)*100,100)),"$","#",2)),100),""))
Ctrl+Shift+Enter

M.

 

[GTO]

You are most welcome Marcelo. I will try yours and report back in just a moment. Given all the input, while not VBA, maybe cheating a bit in using old style code in a Name:

MyRange refers to: =Sheet1!$B$4:$B$7,Sheet1!$E$2:$E$15,Sheet1!$G$6:$G$10,Sheet1!$I$3:$I$20
GetCell refers to: =GET.CELL(2,TEXTREF(SUBSTITUTE(SUBSTITUTE(CELL("address",MyRange),"$",""),",",":"),TRUE))

In any cell, formula: =GetCell

Changing the arguments for SUBSTITUTE as appropriate, does that work on your end?

Mark

 

[GTO]

@Marcelo Branco:

Yes, yours works spot-on

Mark

 

[Marcelo Branco]

Good to know!!!

M.

 

[Marcelo Branco]

You are most welcome Marcelo. I will try yours and report back in just a moment. Given all the input, while not VBA, maybe cheating a bit in using old style code in a Name:

MyRange refers to: =Sheet1!$B$4:$B$7,Sheet1!$E$2:$E$15,Sheet1!$G$6:$G$10,Sheet1!$I$3:$I$20
GetCell refers to: =GET.CELL(2,TEXTREF(SUBSTITUTE(SUBSTITUTE(CELL("address",MyRange),"$",""),",",":"),TRUE))

In any cell, formula: =GetCell

Changing the arguments for SUBSTITUTE as appropriate, does that work on your end?

Mark

Mark,

Worked for me perfectly

M.

 

[GTO]

Thank you for testing Marcelo and have a terrific weekend .

Mark

 

[Marcelo Branco]