Find the last not empty row in a range of cells holding a formula

calendar_today Asked Nov 28, 2015
thumb_up 6 upvotes
history Updated April 16, 2026

Question posted 2015 · +7 upvotes

How can I find the last row in a range of cells that hold a formula, where the result of the formula is an actual value and not empty?

Say in a simplified way that the range of cells ("E1:E10") hold a formula referring to cells A1 through A10 as followed =IF("A1"="","","A1"). But only the cells A1 through A6 have a value filled in, so the result of the formula for cells E7 through E10 will be empty.

Trying to do it with: 

lastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row

results in lastRow having the value of 10. What I want is for the value of lastRow to be 6 in this example.

The actual code is way more complex than this so I can’t just check for the last filled in Row of Column A, as the formulas refer to single cells on different sheets and are added dynamically.

Accepted answer +6 upvotes

I think that more elegant way than was provided by @D_Bester is to use find() option without looping through the range of cells:

Sub test()
    Dim cl As Range, i&
    Set cl = Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    i = cl.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Debug.Print "Last row with data: " & i
End Sub

test

enter image description here

Also, more shorter version of the code which was provided above is:

Sub test2()
    Debug.Print [E:E].Find("*", , xlValues, , xlByRows, xlPrevious).Row
End Sub

2 code variants in this answer

  • Variant 1 — 6 lines, starts with Sub test()
  • Variant 2 — 3 lines, starts with Sub test2()

Excel VBA objects referenced (4)

Top excel-vba Q&A (6)

+6 upvotes ranks this answer #114 out of 136 excel-vba solutions on this site .
excel-vba