Macro Adjustment Needed

[kelly mort]

Hi ,
I found this code on the web that will hide all rows that contain a specific substring in a cell no matter which column the string is found. It's working great but I want it to look for two substrings at a go. I have the feeling I need to adjust this part , maybe:

Sub Test()
    FindAndHide LookingFor:="A"
End Sub

So say ABCDEF, as the above is looking for A, I want the one that can look for A and C or D and F etc at the same time

Sub FindAndHide(LookingFor As String)
Dim i As Long, j As Long
Dim Rng As Range
Dim Arr As Variant
Dim RowContainsString As Boolean


Set Rng = ActiveSheet.UsedRange
Arr = Rng.Value


LookingFor = LCase(LookingFor)


For i = LBound(Arr, 1) To UBound(Arr, 1)
    RowContainsString = False
    For j = LBound(Arr, 2) To UBound(Arr, 2)
        If InStr(LCase(Arr(i, j)), LookingFor) > 0 Then
            RowContainsString = True
            Exit For
        End If
    Next j
    If Not RowContainsString Then Rng.Rows(i).Hidden = True
Next i


End Sub


Sub Test()
    FindAndHide LookingFor:="A"
End Sub

Thanks in advance
Kelly

 

[nemmi69]

Is this what you were after?

Sub FindAndHide(LookingFor As String)
Dim i As Long, j As Long, k As Long
Dim Rng As Range
Dim Arr As Variant
Dim RowContainsString As Boolean
Dim LFarray


Set Rng = ActiveSheet.UsedRange
Arr = Rng.Value


LookingFor = LCase(LookingFor)
LFarray = Split(LookingFor, ",")


For k = LBound(LFarray) To UBound(LFarray)
    For i = LBound(Arr, 1) To UBound(Arr, 1)
        RowContainsString = False
        For j = LBound(Arr, 2) To UBound(Arr, 2)
            If InStr(LCase(Arr(i, j)), LookingFor) > 0 Then
                RowContainsString = True
                Exit For
            End If
        Next j
        If Not RowContainsString Then Rng.Rows(i).Hidden = True
    Next i
Next k


End Sub




Sub Test()
    FindAndHide LookingFor:="A,B,D,F"
End Sub

 

[kelly mort]

It's hiding all the rows.

 

[nemmi69]

Just saw that

This should be better

Sub FindAndHide(LookingFor As String)
Dim i As Long, j As Long, k As Long
Dim Rng As Range
Dim Arr As Variant
Dim RowContainsString As Boolean
Dim LFarray


Set Rng = ActiveSheet.UsedRange
Arr = Rng.Value


LookingFor = LCase(LookingFor)
LFarray = Split(LookingFor, ",")


    For i = LBound(Arr, 1) To UBound(Arr, 1)
        RowContainsString = False
        For j = LBound(Arr, 2) To UBound(Arr, 2)
            For k = LBound(LFarray) To UBound(LFarray)


            If InStr(LCase(Arr(i, j)), LFarray(k)) > 0 Then
                RowContainsString = True
                Exit For
            End If
            Next k
            If RowContainsString = True Then Exit For
        Next j
        If Not RowContainsString Then Rng.Rows(i).Hidden = True
    Next i


End Sub




Sub Test()
    FindAndHide LookingFor:="A,B,D,F"
End Sub

 

[kelly mort]

Okay it's working now.

But I think i was not very clear from the start:
1. It will always look for just two substrings A and B.
2. Both strings must be present in a cell .

Because this is displaying a row with "cat" in a cell. "Cat" does not meet the criteria : only "A" is present. I used the search criteria "A,B" which means A and B must both be present in each cell it chooses to unhide.

Regards
Kelly

 

[nemmi69]

Think this is what you wanted. If a cell contains all the search elements that row remains

Sub FindAndHide(LookingFor As String)
Dim i As Long, j As Long, k As Long
Dim Rng As Range
Dim Arr As Variant
Dim RowContainsString As Boolean
Dim LFarray


Set Rng = ActiveSheet.UsedRange
Arr = Rng.Value


LookingFor = LCase(LookingFor)
LFarray = Split(LookingFor, ",")


    For i = LBound(Arr, 1) To UBound(Arr, 1)
        For j = LBound(Arr, 2) To UBound(Arr, 2)
            RowContainsString = False
            For k = LBound(LFarray) To UBound(LFarray)


                If InStr(LCase(Arr(i, j)), LFarray(k)) > 0 Then
                    RowContainsString = True
                Else
                    RowContainsString = False
                    Exit For
                End If
            Next k
            If RowContainsString = True Then Exit For
        Next j
        If Not RowContainsString Then Rng.Rows(i).Hidden = True
    Next i


End Sub




Sub Test()
    FindAndHide LookingFor:="A,B"
End Sub

 

[kelly mort]

Great! !!!
Now my headache is over.

Thanks a lot for your time and energy.

I just wanna know if I can use:
1. An InputBox
2. Two cells

For the search criteria

That's assign the LookingFor to an InputBox or cells that contain them instead of the static input

Sub Test()
    FindAndHide LookingFor:=InputBox (.....)
End Sub

Okay the InputBox is working but when I click cancel it hides all rows. How do I trap that?

 

[nemmi69]

you could set up a textbox to write to a cell then read that cell
FindAndHide LookingFor:=ActiveSheet.Range("H7").Value

or via an inputbox
FindAndHide LookingFor:=InputBox("Enter values seperated by ',' ")