November 21, 2010

DIY Filter Search

In Excel 2010, one of the handy features that was added is a filter search box for Autofilters.

It's great that the filter actually changes as you type. But I don't always work with 2010, so I decided to make something close. I wanted something where I could simple type in criteria and the filter would work. (Yes, I know there are Text or Number filter options already, but I wanted something even faster and easier - the less clicking and selecting, the better)

This is what I came up with. Select cells in a row above the filter and type in "rCriteria" in the Name Box.

Then open up the Visual Basic Editor (Alt + F11) and paste this code into the appropiate sheet module of your workbook's VBA project.

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

Now enter your criteria. You can type in the whole search string you want or you can use wilcards such as * or >=.

And if you delete the criteria (leaving the cell blank), the filtered column will show all cells once more.

A small saving in time? Perhaps, but it all adds up and helps you keep focus :-)