modify last cell formula

Hi Ian,

Does this work for you?

=ROW(OFFSET(A9,COUNTA(A:A)-2,0))

Sadly not Neeraj. It still displays the last cell containing a formula.

How about this?

=ROW(OFFSET(A9,MATCH(2,1/(A9:A10000<>""))-1,))

That one produced a #N/A error.

Excel 2011 (for Mac) and Excel 2013 (Windows) introduced the ISFORMULA function. You can use that to identify cells with formulas that need to be excluded.

The row number of the last cell with data in column A that does not have a formula may be found using:

=AGGREGATE(14,6,ROW(A:A)*NOT(ISFORMULA(A:A))/(A:A<>""),1)

Select allOpen in new window

If you want a range reference pointing to the cell below that, you might use:

=INDEX(A:A,AGGREGATE(14,6,ROW(A:A)*NOT(ISFORMULA(A:A))/(A:A<>""),1)+1)

Select allOpen in new window

You can use this user defined function by putting it in a code module.
Usage: =lastnf("A")

Function LastNF(strCol As String) As Long
Dim lngLastRow As Long

lngLastRow = Range(strCol & "1048576").End(xlUp).Row
For LastNF = lngLastRow To 1 Step -1
    If Cells(LastNF, 1).HasFormula = False Then
        Exit Function
    End If
Next LastNF
End Function

Select allOpen in new window

I just edited the above.

Bingo... Neeraj your formula worked.

Byundt your formula selected the top row

Thanks guys, Neeraj's formula was perfect.

You're welcome Ian! Glad it worked as desired.

Byundt your formula selected the top row

I tested my suggestion before posting, and it seemed pretty reliable in finding the row number of the last cell with a value and no formula. Could you post the formula you actually used when testing the suggestion?

Byundt,  this was the one.

=AGGREGATE(14,6,ROW(A:A)*NOT(ISFORMULA(A:A))/(A:A<>""),1)