Find the last item and select it

[Djblois]

I know how to find the next item in a workbook but how can I find the last one. ex:

test
test
test
test
test
test
test
report

I would like to find the last test before report with the find command. Is this possible?

Daniel

[austenr]

Here is a Function that you can use to find the last populated row:

Function xlLastRow(Optional WorksheetName As String) As Long

' find the last populated row in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then xlLastRow = 0
End With

End Function

This is a user defined function found in our KB. Don't think you can use the rergular "Find" to do it. HTH

[Djblois]

Thank you but I am not looking for the last populated row. I am looking for the last of a particular text string.

[snicho]

You could use the Match command like this:

Sub FindLastItem()
Dim MatchFound As Integer
On Error Resume Next
MatchFound = Application.Match("report", Columns(1), 0)
If MatchFound = 0 Then
MsgBox "No match found"
Else
MsgBox "Match found on row " & MatchFound
MsgBox "Selecting previous row..."
Range("a1").Offset(MatchFound - 2, 0).Select
End If
End Sub

[mdmackillop]

Two searches I think

Sub FindLastItem()
Dim FirstReport As Range, LastNext As Range, ToFind As String
ToFind = InputBox("Enter words to find", , "Test,Report")
Set FirstReport = Columns(1).Find(what:=Split(ToFind, ",")(1), _
after:=Cells(65536, 1), MatchCase:=False, searchdirection:=xlNext)
Set LastNext = Columns(1).Find(what:=Split(ToFind, ",")(0), _
after:=FirstReport, MatchCase:=False, searchdirection:=xlPrevious)

LastNext.Activate

End Sub