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!
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:
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!
I’d add a CheckBox “Include blank values” and change the code below:
I wouldn’t let the list do the job to include blank values.
A non-VBA way to do this would be Advanced Filter.
I like Advanced Filter 🙂
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.
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).
Thanks for the comment AlexJ. I like to do that too. This routine is intended to be a faster, one-time substitute. I use both on a regular basis.
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.
FilterMatic? Great minds think alike. And I guess ours do too.