Find cells that contain some of text string

[Greemo]

Hello,

Can I have some direction on creating an excel macro to select all the cells that start with a specific string of text? Should I use the find tool? This string of text will always be the start of the cells I'm looking for but will never be the entire contents of the cell.
Any help is appreciated. Thanks.

Greemo

 

[ParamRay]

Method 1

(1) Use the Find command (Home --> Editing --> Find & Select --> Find...).
(2) Type the text you want to find in the Find what textbox followed by an asterisk (*).
(3) Check the Match entire cell contents checkbox and click the Find All button.
(4) Select all the found cell(s) by clicking on the first one in the list and then, while holding down the Shift key, clicking on the last one if the list.


Method 2

Use the following macro:

Sub FindText()

    Dim SearchRange As Range
    Dim FoundRange As Range
    Dim Cell As Range
    Dim Txt As String
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    
    'Set range to search
    Set SearchRange = ActiveSheet.UsedRange
    
    'Set text to search for
    Txt = "hello"
    
    For Each Cell In SearchRange
        If Cell.Value Like Txt & "*" Then
            If FoundRange Is Nothing Then
                Set FoundRange = Cell
            Else
                Set FoundRange = Union(FoundRange, Cell)
            End If
        End If
    Next Cell
    
    If Not FoundRange Is Nothing Then FoundRange.Select

End Sub

 

[Greemo]

Hi gpeacock,

Thank you. The macro works perfectly! Any way to have it only search at non blank cells to save resources?

Thank you,

Greemo

 

[Teeroy]

Try:

Set SearchRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

</pre>

 

[Greemo]

Works great, thank you!

Greemo