Find Last Row ignore formulas

[ejs7597]

I need help finding the last row in a worksheet. I have wrote vba code several ways, and all of the ways find the last row for me, but it finds the last row that contains a formula. I need to ignore the formula, and only find the last row that actually contains a value that the formula is generating.

 

[iggydarsa]

depending on your data size it might not be the most efficient solution but it might help

Sub FindLast()
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim found As Boolean: found = False

While Not found And Not lastRow = 1
    If Range("A" & lastRow).Value = "" Then
        lastRow = lastRow - 1
    Else
        found = True
    End If
Wend

MsgBox lastRow

End Sub

 

[erik.van.geit]

Hi,

You can find the last row with a value using FIND.

Sub test()
Dim LR As Long
    With ActiveSheet.Cells
    LR = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
    End With
    
MsgBox "Last row with data is " & LR, vbOKOnly, "REPORT"
End Sub

kind regards,
Erik

 

[Domski]

Not sure if this is the best way but it might help:

Sub test()
Dim lastRow As Long, myRow As Long
lastRow = Range("A65536").End(xlUp).Row
For myRow = lastRow To 2 Step -1
    If Cells(myRow, 1).HasFormula = False Then
        MsgBox Cells(myRow, 1).Address & " is last value"
    Exit Sub
End If
Next myRow
End Sub

Dom

 

[ejs7597]

Thanks for all of your help. I used Erik's code, remarked out the msgbox, and added ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LR
to set my printarea correctly.
Thanks again.