Find the row number of the last used cell

kgkev

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

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,245
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,672
Messages
5,512,798
Members
408,914
Latest member
ariannem

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top