Snasui Excel Forum

Posted: **Thu Nov 15, 2018 4:07 am**

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

Posted: **Thu Nov 15, 2018 9:53 pm**

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

Posted: **Sat Nov 17, 2018 3:28 am**

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

Posted: **Sat Nov 17, 2018 7:00 am**

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

Posted: **Tue Nov 20, 2018 3:32 am**

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

Posted: **Tue Nov 20, 2018 10:46 pm**

krisney wrote: Tue Nov 20, 2018 3:32 am 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.

Posted: **Thu Nov 29, 2018 9:10 am**

You are awesome Santipong, thank you!
Kristina

Snasui Excel Forum

Constrain VBA search?

Constrain VBA search?

Re: Constrain VBA search?

Re: Constrain VBA search?

Re: Constrain VBA search?

Re: Constrain VBA search?

Re: Constrain VBA search?

Re: Constrain VBA search?