Filter Column by External List

Filter Column by External List

In a comment to my last post Jeff suggested it would be easy to modify that routine to filter a column by an external table, similar to some code he’s written for pivot tables. That would be very useful as part of a model or report, making it easy for users to update by just plopping new values into the table every week or what-have-you.

However, these days I mostly use Excel as a SQL development environment, running the SQL right in a table and then filtering and pivoting the results to figure out if my queries are working. For that reason, I really just wanted a form with a textbox where I could drop a bunch of values and filter by them. That utility, combined with the built-in filtering tools and the code from my last post would give me a lot of ways to parse my data.

And now, thanks to the wonder that is VBA, I have that utility. What’s more, so do you!

filter form

Above is the form in all its simple glory. I’d show an “After” picture, but I trust your imagination can supply that.

The UserForm VBA

Here’s the code for the main routine. It looks a lot like that in my last post, so be sure to check that out if you haven’t already:

Sub FilterList()
Dim FilterValues() As String
Dim ColNumberInFilterRange As Long
Dim FilterRange As Excel.Range
Dim InTable As Boolean
Dim CollUniqueValues As Collection
Dim i As Long

If ActiveSheet Is Nothing Then
    MsgBox "No active worksheet."
    Exit Sub
End If
If Me.txtValues.Text = "" Then
        MsgBox "Enter at least one value to filter by"
        Exit Sub
End If
With Selection
    If .Cells.Count = 1 And IsEmpty(ActiveCell) Then
        MsgBox "Please select a cell within one or more cells with data."
        Exit Sub
    End If
    If Union(ActiveCell.EntireColumn, .EntireColumn).Address <> ActiveCell.EntireColumn.Address Then
        MsgBox "Only select from one column"
        Exit Sub
    End If
    'Set the range to be filtered depending on whether it's a Table or not
    If Not ActiveCell.ListObject Is Nothing Then
        Set FilterRange = ActiveCell.ListObject.Range
        InTable = True
    Else
        Set FilterRange = ActiveCell.CurrentRegion
    End If
    If Union(Selection, FilterRange).Address <> FilterRange.Address Then
        MsgBox "Please make sure all cells are within the same table or contiguous area."
        Exit Sub
    End If
    'If not in a table and we're filtering a different area than currently filtered
    'then turn the existing AutoFilter off, so no error when the new area gets filtered.
    If Not InTable And ActiveSheet.AutoFilterMode Then
        If ActiveSheet.AutoFilter.Range.Address <> .CurrentRegion.Address Then
            ActiveSheet.AutoFilterMode = False
        End If
    End If

    FilterValues = Split(Me.txtValues.Text, vbCrLf)
    'Try to add every selected value to a collection - only unique values will succeed
    Set CollUniqueValues = New Collection
    For i = LBound(FilterValues) To UBound(FilterValues)
        On Error Resume Next
        CollUniqueValues.Add FilterValues(i)
        On Error GoTo 0
    Next i
    'Transfer the collection to an array for the AutoFilter function
    ReDim FilterValues(1 To CollUniqueValues.Count)
    For i = LBound(FilterValues) To UBound(FilterValues)
        FilterValues(i) = CollUniqueValues(i)
    Next i
    'Determine the index of the column to be filtered within the FilterRange
    ColNumberInFilterRange = (.Column - FilterRange.Columns(1).Column) + 1
    FilterRange.AutoFilter Field:=ColNumberInFilterRange, Criteria1:=FilterValues, Operator:=xlFilterValues
End With
End Sub

There’s one issue with this code that I haven’t solved yet. When I paste into the textbox it always puts a line feed after the last item. Unless I backspace up to the end of the last item the filter also includes blank cells, not what I’d usually want. On the other hand I don’t think I just want to get rid of that last line feed in my code because I might actually want to include a null value/blank in the filter.

Download

This download has the resizable (thanks to Chip Pearson) form for you to play with.

Next Time

Filter Using Common Household Objects!

7 thoughts on “Filter Column by External List

  1. I’d add a CheckBox “Include blank values” and change the code below:

    If FilterValues(i) <> "" Then CollUniqueValues.Add FilterValues(i)

    I wouldn’t let the list do the job to include blank values.

    • You are right of course MF. I always forget about advanced filter. Especially for repeated filtering of the same columns that’s a better solution. I think of this as a more on-the-fly way to do it.

  2. Doug – anonther nonVBA method:
    I like to add a helper column to the table which uses Match from the column I’m checking to the list of values. Then filter the extra column (iferror() makes it cleaner, of course).

  3. Cool! Sorry, missed this until now.
    @MF: Problem with the Advanced Filter is that it doesn’t. Filter, that is. Rather, it completely removes any existing filters, and then hides any rows that don’t meet the criteria–which just gives the impression that the Table has been filtered. Try to add or remove items by using the filter dropdown, and you’ll find you can’t. Even worse, any information that you currently have displayed surrounding that Table will now also be hidden. Not to mention that that Advanced Filter is downright painful to setup and to use.

    I’ve recently beefed up the Filter Pivot code that Doug linked to above, so that it handles Tables too. Have also changed it’s name to FilterMatic accordingly. Will be blogging about it fairly soon. I’ve also got some great code to invert a filter selection in a table. That is tricky, indeed…unless I’ve overlooked a much simpler solution.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>