You can start at the bottom and search for the next to find the top one. Then
find the previous instance:

Option Explicit
Sub testme01()

Dim RngToSearch As Range
Dim LookForWhat As String
Dim FoundCellTop As Range
Dim FoundCellBot As Range

LookForWhat = "test"

With Worksheets("sheet1")
.Select 'so the range can be selected later
Set RngToSearch = .Range("a:a") 'I used column A.
With RngToSearch
Set FoundCellTop = .Cells.Find(what:=LookForWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCellTop Is Nothing Then
'not found, do nothing
Else
Set FoundCellBot = .FindPrevious(FoundCellTop)
End If
End With

If FoundCellTop Is Nothing Then
'foundcellbot will also be nothing
'so do nothing
MsgBox "not found"
Else
.Range(FoundCellTop, FoundCellBot).Select
If FoundCellTop.Address = FoundCellBot.Address Then
MsgBox "Only one found"
End If
End If
End With

End Sub


You could go the opposite direction, too.



Noemi wrote:

Hi

I am after the VBA code which will allow me to find the first text eg "Test"
and then find the last text eg "Test" and highlight from the first "Test" to
the last "Test" found.

I understand about using the find and findlast but I dont know how I combine
the 2 so I can select both cells plus all the cells inbetwwen them.

I hope someone can help.

Thanks
Noemi

--

Dave Peterson