# Need formula for position of Last Row in a Range

#### jbesclapez

##### Board Regular
Hello Mr Excel!

I have a formula that looks like the one I need, but it only does the job for one Column at a time and not a multi column Range.
=+LOOKUP(2,1/(B4:B10<>""),ROW(B4:B10))
From that i get the position of the last non empty member
I would like to get the same but instead of B4:B10 it should be B4:Z10 ...

Thanks.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Anthony47

##### Well-known Member
For example
Excel Formula:
``=MAX(IF(B4:Z10<>"",ROW(B4:B10),""))``
It has to be confirmed using Contr-Shift-Enter, not only Enter

Bye

#### jbesclapez

##### Board Regular
For example
Excel Formula:
``=MAX(IF(B4:Z10<>"",ROW(B4:B10),""))``
It has to be confirmed using Contr-Shift-Enter, not only Enter

Bye
Thanks Anthony! Really appreciated.... BUT... (the famous "but" that means trouble ... could we do this without array formulae?
Thanks

#### mart37

##### Well-known Member
=SUMPRODUCT(LARGE((B4:Z10<>"")*ROW(B4:Z10),1))
or
=SUMPRODUCT(MAX((B4:Z10<>"")*ROW(B4:Z10)))

#### jbesclapez

##### Board Regular

=SUMPRODUCT(LARGE((B4:Z10<>"")*ROW(B4:Z10),1))
or
=SUMPRODUCT(MAX((B4:Z10<>"")*ROW(B4:Z10)))
Thanks Mart37 it is exactly that! Have you got any idea on which of those 2 formulae is the fastest?

#### Anthony47

##### Well-known Member
Thanks Anthony! Really appreciated.... BUT...
Just a curiosity, why array formulas are so bad to avoid them?
You got from MART the Sumproduct solution; it works because Sumprodct is intrinsically an array formula

Bye

#### jbesclapez

##### Board Regular
Just a curiosity, why array formulas are so bad to avoid them?
You got from MART the Sumproduct solution; it works because Sumprodct is intrinsically an array formula

Bye
It is because I am building this in excel, but ultimately it will be in a "look alike" software like spreadsheet...

Replies
5
Views
97
Replies
4
Views
108
Replies
10
Views
271
Replies
3
Views
137
Replies
3
Views
211

1,137,113
Messages
5,679,697
Members
419,850
Latest member
Cbell07958

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