Function Last Row()

J

Jock

Code below is by Ron de Bruin and will find the last row with anything in it.

Function LastRow(Sh As Worksheet)
On Error Resume Next
LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

My sheet however has columns of formulae and this code treats them as being
valid data.
Can this be adapted to ignore all formulae and only recognise values?
Thanks,
 
J

Jacob Skaria

Try the below...Modified the function to suit your requirement...

Function LastRow(Sh As Worksheet)
Dim varFound As Range
On Error Resume Next
Set varFound = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If varFound.HasFormula Then
Do: Set varFound = Sh.Cells.FindPrevious(varFound)
Loop Until varFound.HasFormula = False
End If
LastRow = varFound.Row
On Error GoTo 0
End Function

If this post helps click Yes
 
R

Rick Rothstein

Just a note on your modification and Ron's original code... you can omit the
After, LookAt and MatchCase arguments. If omitted, the default for the After
argument is the first cell in the range; since you are searching for *any*
character, it doesn't matter whether LookAt is set to all or part of the
text in the cell; and, again, since we are searching for *any* character, it
doesn't matter if that text is upper or lower case. So, the simplified
statement becomes this...

Set varFound = Sh.Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top