I have a spreadsheet here that has 3 columns, and below each column lots of text.

How can create a filter that I can find all occurrences of a given text in any of these columns and show only the lines that has it no matter the column that it has been found.

  • Excel can't do this type of filter, one workaround can be to create a helper column where you concatenate content of the other columns and filter there. Jan 5 '16 at 11:58
  • I tried to select the entire sheet and apply a custom filter but did not find how to apply the rule the other column than the one that is selected. Jan 5 '16 at 12:04
  • Ok, I did this and works nicely. Now how can I create something like a search box and apply it to the filter automatically? Jan 5 '16 at 12:09

Ok, what I just did was create a filter and a macro to set the filter as the value of a certain cell.

follows the code I copied from the internet and changed a little bit.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iFilterColumn As Integer
    Dim rFilter As Range
    Dim sCriteria As String
    On Error Resume Next
    With Target
        Set rFilter = .Parent.AutoFilter.Range
        iFilterColumn = .Column + 1 - rFilter.Columns(1).Column
        If Intersect(Target, Range("rCriteria")) Is Nothing Then GoTo Terminator
        Select Case Left(.Value, 1)
        Case ">", "<"
            sCriteria = .Value
        Case Else
            sCriteria = "=*" & .Value & "*"
        End Select
        If sCriteria = "=" Then
            .Parent.Range(rFilter.Address).AutoFilter Field:=iFilterColumn
            .Parent.Range(rFilter.Address).AutoFilter Field:=iFilterColumn, Criteria1:=sCriteria
        End If
    End With
    Set rFilter = Nothing
    On Error GoTo 0
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.