Find the row number of the last used cell

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,285
Office Version
  1. 365
Platform
  1. Windows
In this example the answer should be 104.

Can I do this with a forumla?
Excel Workbook
ABCD
875957Repair
88
89596325/06/2010Repairno
90596425/06/2010Repairno
915965
92596829/06/2010Supplier
935970RepairNo
945971
955972
965973
97597412/07/2010SupplierYes
985975
99597614/07/2010SupplierNo
100597921/07/2010SupplierNo
1015980
102598123/07/2010SupplierNo
1035982
104598326/07/2010RepairNo
1055984
1065985
1075986
1085987
1095988
1105989
1115990
1125991
1135992
1145993
Active rejects
Excel 2000
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
To VoG,

Just tried the formula you supplied and it works fine, but could you please explain the significance of 9.9E+307?

Thank you from someone always in awe of your knowledge.

Regards
Paul
 
Upvote 0
Hi

9.99999999999999E+307 is the largest number you can have in Excel. I'm just too lazy to type all those 9s.

The formula returns the last numeric value that is smaller than or equal to this number. As it is unlikely that the column will contain this value, it will find the last value that is smaller, that is the last numeric value (as all the numeric values will be smaller).
 
Upvote 0
Peter,

Hadn't really though about it - but what if the next smallest number (ie the biggest number) isn't in bottom cell?
 
Upvote 0
That doesn't matter. It finds the bottom number. Try adding an earlier date at the bottom of column B to demonstrate.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top