Find Last Row ignore formulas

ejs7597

New Member
Joined
Sep 25, 2006
Messages
35
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

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
depending on your data size it might not be the most efficient solution but it might help

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

You can find the last row with a value using FIND.
Code:
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Not sure if this is the best way but it might help:

Code:
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

New Member
Joined
Sep 25, 2006
Messages
35
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.
 

Forum statistics

Threads
1,085,846
Messages
5,386,318
Members
401,993
Latest member
CVBALeo

Some videos you may like

This Week's Hot Topics

Top