Function Last Row()

[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,

 

[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

 

[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)

 

[Jacob Skaria]

Thanks Rick..I didnt bother to modify those..

If this post helps click Yes