range selection based on text value

[JeffAlton]

I need to select a range based on the text in cells.

For example if I have five cells in a columm that have "Oregon" in them I want to be able to select all five with a macro.

 

[PHV]

Have you tried to play with AutoFilter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[byundt]

Why do you want to select the cells? It is almost always better (faster running code, fewer problems) to avoid selecting cells.

If for some reason you don't want to use the AutoFilter, then you can also create a range containing them using the Find function. This is more efficient than looping through the cells.[vba]Sub FindStrings()
Dim s As String
Dim cel As Range, rg As Range, rgFound As Range
If Selection.Cells.Count > 1 Then Set rg = Selection Else Set rg = ActiveCell.EntireColumn
s = InputBox("What string do you want to find")
Set rgFound = rg.Find(s, lookat:=xlPart, MatchCase:=False)
If Not rgFound Is Nothing Then
Set cel = rgFound
Do
Set cel = rg.FindNext(cel)
If cel.Address = rgFound.Cells(1, 1).Address Then Exit Do
Set rgFound = Union(rgFound, cel)
Loop
rgFound.Select 'Don't know why you'd ever want to select cells, but here it is
End If
End Sub[/vba]Brad