Search box to hide rows that don't contain

[JasonBing]

I have a sheet with a list of customer orders called (FTBJOB2) the data is contained in cells A to AP of the table starting from row 13

I Know how to make a combo box that places whatever is typed, in a specific cell. In this case it will put the result in cell ("AQ12")

I would love a code that would dynamically hide all rows from Row 13 down to 997 that don't contain the text in cell ("AQ12") in any of the cells of that row

This is to create a google style search box using anything to do with the customer orders

I already have buttons to hide rows based on the value in column D (this contains the job status) so I will need to unhide these first in the code so they see result for each job.

This will let the guys see orders that only pertain to whatever they are searching for.

is this a code that will live in the worksheet or in a separate macro?

Any help would be greatly appreciated

Thanks wonderful people

Regards

Jason Bing

 

[Yongle]

This works for me
- rows 13:997 are hidden
- rows looped and searched to find required text and first found cell in each row is added to range Vizible
- rows in range Vizible unhidden

Place code in the SHEET module (avoids need to qualify range references with sheet)
- call it after value in AQ12 has been updated

Sub HideRows()
    Dim r As Long, What As String, Where As Range, Found As Range, Vizible As Range
    Rows("13:997").EntireRow.Hidden = True
    What = "*" & Range("AQ12") & "*"
    Set Where = Range("A1:AP1")
    
    For r = 12 To 996
        On Error Resume Next
        Set Found = Nothing
        Set Found = Where.Offset(r).Find(What)
        On Error GoTo 0
        If Not Found Is Nothing Then
            If Vizible Is Nothing Then Set Vizible = Found Else Set Vizible = Union(Vizible, Found)
        End If
    Next r
    
    If Not Vizible Is Nothing Then Vizible.EntireRow.Hidden = False
End Sub

 

[JasonBing]

Wow that works very well.

Added a few things to speed it up and deal with sheet protection

Screen updating = false etc

Thank you very much. its help from people like you that really show people the power of excel

Thanks

Jason Bing

 

[Yongle]

Thanks for the feedback

 

[JasonBing]

Is there any way to get this code to look at a textbox value not ("AQ12")?

Thanks Jason Bing

 

[Yongle]

If it's a an active-X textbox embedded in the worksheet try this method ..

Replace

What = "*" & Range("AQ12") & "*"

With

What = "*" & [COLOR=#ff0000]TextBox1[/COLOR].Text & "*"