Find the row number of the last used cell

kgkev

Well-known Member
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Do u mean used as in change in value in the worksheet?

Possibly

=MATCH(9.9E+307,B:B)

spot on thanks

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

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

Hi VoG,
Thanks for that, once again your knowledge transcends all.
Regards
Paul

Peter,

Hadn't really though about it - but what if the next smallest number (ie the biggest number) isn't in bottom cell?

That doesn't matter. It finds the bottom number. Try adding an earlier date at the bottom of column B to demonstrate.

Replies
4
Views
211
Replies
6
Views
352
Replies
4
Views
134
Replies
6
Views
193
Replies
2
Views
277

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

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.

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