Need formula for position of Last Row in a Range

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
243
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 ...

Can you please do your magic MrExcel? :)

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
Joined
Mar 29, 2006
Messages
2,151
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
Joined
Feb 6, 2010
Messages
243
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?
I am sorry for not mentioning that earlier in my requirements. I totally forgot about this!
Thanks
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
=SUMPRODUCT(LARGE((B4:Z10<>"")*ROW(B4:Z10),1))
or
=SUMPRODUCT(MAX((B4:Z10<>"")*ROW(B4:Z10)))
 
Solution

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
243

ADVERTISEMENT

=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
Joined
Mar 29, 2006
Messages
2,151
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
Joined
Feb 6, 2010
Messages
243
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...
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top