Macro for filter

[Cristy0505]

Hello again.

I have the following script for filter in excel.

Sub Pesq1_Clique()
    
    sbx = InputBox("Type desired word")
    If sbx = cancel Then 
   Exit Sub
End If

        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=1, Criteria1:=sbx _
        , Operator:=xlAnd
        
        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=2, Criteria1:=sbx _
        , Operator:=xlAnd
        
        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=3, Criteria1:=sbx _
        , Operator:=xlAnd
        
        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=4, Criteria1:=sbx _
        , Operator:=xlAnd
        
        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=5, Criteria1:=sbx _
        , Operator:=xlAnd
        
        ActiveSheet.Range("$B$3:$G$92").AutoFilter Field:=6, Criteria1:=sbx _
        , Operator:=xlAnd
        
        MsgBox "No more results"
        

End Sub

The way it is now it's just hiding everything because if first, second, third and so on columns does not contain searched word it hides all the rows in range.

I would like the macro to search first column if word not found, ignore and go next column from column B to G and show all the rows that contain the searched word (or expression or number or part of expression/ number) .

To resume I'm trying to find a way to make a custom search that show all the rows that contain searched item and hide other rows that doesn't have searched word...

Could somebody help me on that?

Thanks in advance.

[tigeravatar]

Cristy0505,

Here's one way. Change lLook to the desired search method (exact or partial matches):

Sub Pesq1_Clique()
    
    Dim rngUnHide As Range
    Dim rngFound As Range
    Dim lLook As XlLookAt
    Dim strFirst As String
    Dim strFind As String
    
    strFind = InputBox("Type desired word", "Find Text")
    If Len(strFind) = 0 Then Exit Sub   'Pressed cancel
    
    'xlWhole for exact match
    'xlPart for partial match
    lLook = xlWhole
    
    With Range("B4:G92")
        .EntireRow.Hidden = False
        Set rngFound = .Find(strFind, .Cells(.Cells.Count), xlValues, lLook)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Set rngUnHide = rngFound.EntireRow
            Do
                Set rngUnHide = Union(rngUnHide, rngFound.EntireRow)
                Set rngFound = .Find(strFind, rngFound, xlValues, lLook)
            Loop While rngFound.Address <> strFirst
        End If
        .EntireRow.Hidden = True
    End With
    
    If Not rngUnHide Is Nothing Then rngUnHide.EntireRow.Hidden = False
    
    Set rngUnHide = Nothing
    Set rngFound = Nothing
    
End Sub

[Cristy0505]

Thank you for your time and attention.

I don't know why but it isn't showing anything even if the worksheet have the searched word or number. It is hiding everything instead.

I have uploaded the worksheet here: http://temp-share.com/show/dPf3UC95W so you can check it.

Kind regards.

[tigeravatar]

Cristy0505,

You probably just need to change lLook from xlWhole to xlPart...

[Cristy0505]

Cristy0505,

You probably just need to change lLook from xlWhole to xlPart...

Thank you. It works!

How do I do after search to show all the rows back again?

[tigeravatar]

Select all cells -> right click a row -> unhide

[Cristy0505]

Select all cells -> right click a row -> unhide

Thank you!

[tigeravatar]

You're very welcome