Constrain VBA search?

[krisney]

Hello, in your example of MyAdvancedSearch, is there a way to constrain the search to look only at specific columns, rather than the whole range?

Thank you, Kristina

[snasui]

Please attach your example file and point to a column that you want to search.

[krisney]

Hello, I've attached the file. The search box is in B1.

Problem 1: It doesn't seem to launch a search every time though. I have to click in the box and hit the Delete button for all of the data rows to be returned to normal (possibly I've done something wrong).

Problem 2: And for some reason it always includes the first row of data, even if the Search Term is not located in that row.

Problem 3: And if I could search for 3 keywords, that would be awesome

Problem 4: Constrain the search to only specific Columns?

Thank you for trying to help!
Kristina

[snasui]

Try this code.

Problem 1: In case of B1 is blank this code don't search anything and show all rows of data.

Problem 2: Because the old code starts with Row 4 instead of Row 3.

Problem 3: With the code below you can search many words separated by a comma (ex. Express, issue, BGP, recovery).

Problem 4: The code below will be searching on column C, you can change to any column by changing this line.
For Each r In .Range("c3", .Range("c" & .Rows.Count).End(xlUp))

Code: Select all

Sub MyAdvancedSearch()
    Application.ScreenUpdating = False
    Dim r As Range ', v As String
    Dim s As Variant, i As Integer
    Dim j As Integer
    With Sheets("Data")
        .Range("c2").CurrentRegion.EntireRow.Hidden = False
        If .Range("b1").Value = "" Then Exit Sub
        s = Split(.Range("b1").Value, ",")
        For Each r In .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
            j = 0
            For i = 0 To UBound(s)
                If InStr(1, UCase(r.Value), Application.Trim(UCase(s(i)))) > 0 Then
                    j = j + 1
                End If
            Next i
            If j = 0 Then
                r.EntireRow.Hidden = True
            End If
        Next r
    End With
    Application.ScreenUpdating = True
End Sub

[krisney]

Wow - thank you! I have no idea what this new code means (I was just using yours from YouTube)! I'll have to do some research.
How could I change this line to search more than 1 column, or the whole table?

For Each r In .Range("c3", .Range("c" & .Rows.Count).End(xlUp))

Thank you VERY much - Kristina

[snasui]

How could I change this line to search more than 1 column, or the whole table?

You can change to the code below:

Code: Select all

Sub MyAdvancedSearch()
    Application.ScreenUpdating = False
    Dim r As Range, rln  As Range
    Dim s As Variant, i As Integer
    Dim j As Integer, str As Variant
    With Sheets("Data")
        .Range("c2").CurrentRegion.EntireRow.Hidden = False
        If .Range("b1").Value = "" Then Exit Sub
        s = Split(.Range("b1").Value, ",")
        For Each r In .Range("a3", .Range("a" & .Rows.Count).End(xlUp))
            j = 0
            For Each rln In r.Resize(1, 20)
                str = str & rln.Value
            Next
            For i = 0 To UBound(s)
                If InStr(1, UCase(str), Application.Trim(UCase(s(i)))) > 0 Then
                    j = j + 1
                End If
            Next i
            If j = 0 Then
                r.EntireRow.Hidden = True
            End If
        Next r
    End With
    Application.ScreenUpdating = True
End Sub

Sorry to let you know about the VBA question. One of the board rules members has to try to change the code from the answer to achieve the goal. If the issue persists or a new problem arises, you can request to solve the problem without restriction.

[krisney]

You are awesome Santipong, thank you!
Kristina