Filter rows by cells containing a certain character sequence (XL 2010)

[dan25]

Version: Excel 2010</SPAN>

Hi,</SPAN>

My challenge is that I need a way to filter the rows of a spreadsheet with the criteria being any cell that contains a certain character sequence.</SPAN>


For example:</SPAN>

The criteria is: '12345' (without quotes). Rows that contain a cell with an instance of the criteria in its contents should be filtered:</SPAN>

Examples of a cell's contents (one cell example per line).</SPAN>

12345</SPAN>
Wr-12345</SPAN>
This argument was not listed Wr#12345</SPAN>
___asdsad343________12345__sd</SPAN>

All of these cells match the criteria </SPAN>

By 'filter' I don't mean it needs to be literally filtered, but basically the function I need here in plain english, is:</SPAN>

Something that can search for some criteria (12345 in the example above) in a bunch of rows/columns, and display the results (the rows with the cells matching the search criteria).</SPAN>

Wheter this can be done with a macro or an existing excel funtion, I am unsure. Help from the experts here would be great!</SPAN>

Any help is appreciated!!</SPAN>

Daniel</SPAN>

 

[RonB1111]

Turn on Excel Auto Filters and do a Custom Filter for 12345. See this for details: http://www.contextures.com/xlautofilter01.html

 

[Warship]

maybe...

Sub FindCriteria()
    Dim sCriteria As String
    Dim r As Long, i As Long
    Dim fnd As Range
    Dim rgSource As Range
    Dim shSource As Worksheet
    Dim shDestin As Worksheet
    
    Set shSource = Sheets("Sheet1")
    Set rgSource = shSource.Cells.CurrentRegion
    Set shDestin = Sheets("Sheet2")
    
    shDestin.Cells.Clear
    
    sCriteria = InputBox("Criteria?:")
    
    For i = 1 To rgSource.Rows.Count
        Set fnd = rgSource.Rows(i).Find(sCriteria)
        If Not fnd Is Nothing Then
            r = r + 1
            fnd.EntireRow.Copy shDestin.Cells(r, 1)
        End If
    Next i
End Sub