FindFirst, FindLast and select

[Guest]

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]

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.

 

[Guest]

If you have the Firstcell and LastCell, then all you should need is
Range(FirstCell,LastCell).Select



Sub SelectRange()
Dim MyRange As Range
Dim FirstCell As Range
Dim LastCell As Range
Const Criteria = "YourCriteria"

Set MyRange = Selection 'Or whatever else you want

Set LastCell = MyRange.Find(what:=Criteria, _
after:=MyRange.Cells(1, 1), searchdirection:=xlPrevious)
Set FirstCell = MyRange.FindNext(LastCell)

If Not FirstCell Is Nothing And _
Not LastCell Is Nothing Then _
Range(FirstCell, LastCell).Select

End Sub