Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FindFirst, FindLast and select
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FindFirst, FindLast and select
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FindFirst, FindLast and select
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|