Find the row number of the last used cell

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,251
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095

ADVERTISEMENT

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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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).
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095

ADVERTISEMENT

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

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,251
Peter,

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
That doesn't matter. It finds the bottom number. Try adding an earlier date at the bottom of column B to demonstrate.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,011
Members
430,103
Latest member
BIGGAZ

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