locked
Search with a string and display rows RRS feed

  • Question

  • Madam/Sir,

    I have a database in Excel.It has been prepared  in a unprofessional manner.The fields are not filled properly so that we can filter the  rows efficiently.The key words are found anywhere within the cell.That is, if I want to filter the rows containing  the word "Apple", it will not be available at the beginning of the text entered in a cell.it will be  typed as "Apple","An Apple","The Apple" or "fruit is Apple". Moreover, there are also  punctuation errors like merger of two words at the beginning or end like"AnApple",anApple","Anapple" , "Applefruit" or "appleFruit"

    I want to classify data on the basis of a string( for example "apple", not case sensitive) available in a column.

    So, I need a macro to display all rows that contain a specific string in a particular column.The macro should ask the "String" and "column header" to input into a text box.

    Please help.


    ஜெ.இரவிச்சந்திரன்

    Thursday, September 27, 2018 8:29 AM

Answers

  • No need for VBA. Use AutoFilter.

    1. Click on cell which is the heading of column to be filtered. (in Example: A5)
    2. Press SHIFT-CTRL-L to start AutoFilter (Downward triangle in A5)
    3. Click AutoFilter triangle then select Text Filters > Contains
    4. Enter apple in Custom AutoFilter dialog as shown below

    But if you really need a VBA routine:

        Dim vResponse As Variant
        Dim lCol As Long
        Dim sSearch As String
    '   Get Column#
        vResponse = InputBox("Enter column # to search:", "Search")
        If vResponse <> vbNullString Then
            If IsNumeric(vResponse) Then
                lCol = CLng(vResponse)
            '   Get Search String
                vResponse = InputBox("Enter string to search for:", "Search")
                If vResponse <> vbNullString Then
                    sSearch = Trim(CStr(vResponse))
                    If Not ActiveSheet.AutoFilterMode Then [A5].CurrentRegion.AutoFilter
                    [A5].CurrentRegion.AutoFilter Field:=lCol, Criteria1:="=*" & sSearch & "*", Operator:=xlAnd
                End If
            End If
        End If


    CHatmaker


    • Edited by CHatmaker Thursday, September 27, 2018 12:25 PM
    • Marked as answer by rjagathe Friday, September 28, 2018 11:17 AM
    Thursday, September 27, 2018 12:13 PM

All replies

  • No need for VBA. Use AutoFilter.

    1. Click on cell which is the heading of column to be filtered. (in Example: A5)
    2. Press SHIFT-CTRL-L to start AutoFilter (Downward triangle in A5)
    3. Click AutoFilter triangle then select Text Filters > Contains
    4. Enter apple in Custom AutoFilter dialog as shown below

    But if you really need a VBA routine:

        Dim vResponse As Variant
        Dim lCol As Long
        Dim sSearch As String
    '   Get Column#
        vResponse = InputBox("Enter column # to search:", "Search")
        If vResponse <> vbNullString Then
            If IsNumeric(vResponse) Then
                lCol = CLng(vResponse)
            '   Get Search String
                vResponse = InputBox("Enter string to search for:", "Search")
                If vResponse <> vbNullString Then
                    sSearch = Trim(CStr(vResponse))
                    If Not ActiveSheet.AutoFilterMode Then [A5].CurrentRegion.AutoFilter
                    [A5].CurrentRegion.AutoFilter Field:=lCol, Criteria1:="=*" & sSearch & "*", Operator:=xlAnd
                End If
            End If
        End If


    CHatmaker


    • Edited by CHatmaker Thursday, September 27, 2018 12:25 PM
    • Marked as answer by rjagathe Friday, September 28, 2018 11:17 AM
    Thursday, September 27, 2018 12:13 PM
  • Hi rjagathe,

    Did CHatmaker's  answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    Friday, September 28, 2018 8:29 AM