2

I need a formula (not VBA) that will return the row number of the last used cell in a worksheet.

I have data like:

enter image description here

Not all columns have data and the columns are not filled with the same number of rows.

If I was dealing with only a single column (say column F), I would use:

=SUMPRODUCT(MAX((ROW(Sheet1!F:F))*(Sheet1!F:F<>"")))

which yields 13, but the data spans many columns in Sheet1

If I could use VBA rather than a formula, I would use something like:

Sub GetLastRow()
    Dim LastRow As Long, rng As Range
    Set rng = Sheets("Sheet1").Cells
    LastRow = rng.Find(what:="*", after:=rng(1), searchdirection:=xlPrevious).Row
    MsgBox LastRow
End Sub

I think I am missing something very simple.

  • Take a look: rondebruin.nl/win/s9/win005.htm – duDE Jan 3 '17 at 15:36
  • @duDE Thanks for responding....sadly, these solutions involve VBA – Gary's Student Jan 3 '17 at 15:37
  • btw a non array formula type to find the last row in a specific column: =MAX(IFERROR(MATCH("ZZZ",F:F),0),IFERROR(MATCH(1E +99,F:F),0)) – Scott Craner Jan 3 '17 at 15:39
  • @ScottCraner Thank you Scott...do you think a formula solution is possible ?? If it helps only the first 42 columns are populated. – Gary's Student Jan 3 '17 at 15:40
  • 1
    @ScottCraner You are correct.....if you post an Answer, I'll accept.... THANKS ! – Gary's Student Jan 3 '17 at 15:49
2

Either use your SUMPRODUCT:

=SUMPRODUCT(MAX((ROW(Sheet1!A:AP))*(Sheet1!A:AP<>"")))

or this AGGREGATE:

=AGGREGATE(14,6,ROW(A:AP)/(A:AP<>""),1)

If you can limit the number of rows it will help speed up the formulas as they are array type formulas.

  • Thank you...while this may seem simple,...I was stumped for a non-VBA approach. – Gary's Student Jan 3 '17 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.