Find last row with data

[FryGirl]

Need to find the last row in column C, but find the last filled cell and not just the last row.

In column C there is a formula down to row 7, but based on the formula in column C there are only results down to row 3. With the macro below the Msgbox returns 7 but need to return 3.

Sub findlast()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    MsgBox lr
End Sub

 

[jasonb75]

Need to find the last row in column C, but find the last filled cell and not just the last row.

In column C there is a formula down to row 7, but based on the formula in column C there are only results down to row 3. With the macro below the Msgbox returns 7 but need to return 3.

Sub findlast()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    MsgBox lr
End Sub

Technically what happens is correct, if cells C4 to C7 hold a formula that shows a blank then they are filled with the formula.

You can get the result you want with

Sub findlast()
    Dim lr As Long
    lr = Application.WorksheetFunction.CountIf([C:C], "<=>")
    MsgBox "C" & lr
End Sub

But that will not work if, for example C1 and C3 hold values, but C2 in blank.

 

[FryGirl]

Thanks Jason that makes sense and it gets me further down the road

 

[jasonb75]

Try

Sub findlast()
    Dim lr As Long
        For lr = Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1
            If Len(Range("C" & lr)) > 0 Then Exit For
        Next
    MsgBox "C" & lr
End Sub

Think that should overcome any problems.