Autofilter Archives - yoursumbuddy

Remove Filters From Other Columns

Posted on October 1, 2015 by Doug Glancy

Here’s another in what’s turning out to be a series of posts on AutoFiltering. This time it’s a bit of code to remove filters from all the columns except those that are part of the current selection. The idea is kind of like “Close Other Tabs” in Fireox or Notepad++. It works for Tables and plain old filtered ranges. I wrote it the other day while working on a mile-wide list with various fancy filters, most of which I wanted to get rid of.

It’s not much code, and hopefully it’s self-explanatory. (If not, you know where to find me.)

Sub ClearFiltersFromOtherColumns()
Dim i As Long
Dim AutoFltr As Excel.AutoFilter

If ActiveSheet Is Nothing Then
MsgBox "No active worksheet."
Exit Sub
End If
'Determine the range with the filter depending on whether it's a Table or not
If Not ActiveCell.ListObject Is Nothing Then
Set AutoFltr = ActiveCell.ListObject.AutoFilter
Else
Set AutoFltr = ActiveCell.Parent.AutoFilter
End If
If AutoFltr Is Nothing Then
MsgBox "The selection is not within filtered table or range"
Exit Sub
End If
If Union(Selection, AutoFltr.Range).Address <> AutoFltr.Range.Address Then
MsgBox "Please make sure all cells are within the same table or filtered area."
Exit Sub
End If

For i = 1 To AutoFltr.Filters.Count
If AutoFltr.Filters(i).On And _
(Intersect(Selection.EntireColumn, AutoFltr.Range.Columns(i)) Is Nothing) Then
AutoFltr.Range.AutoFilter Field:=i
End If
Next i
End Sub

The part I like is how it narrows down to a aingle AutoFilter object regardless of whether you’re working with a ListObject or just a filtered range. I’m not sure I knew there was such an object until I read Mike Alexander’s post on A Better Way to Copy Filtered Rows Using VBA.

Filter Column by External List

Posted on May 13, 2015 by Doug Glancy

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:

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!

Filter Column By All Selected Values

Posted on May 3, 2015 by Doug Glancy

Excel’s Filter by Selected Cell’s Value is a popular command, and for good reason. It’s a quick way to filter the data that you’re looking at. There are times when I’d like to take it one step further and filter by the values of multiple cells. So I wrote some code and added a “Filter By All Selected Values” item to my right-click menu. It works just like Filter by Selected Cells except you can select multiple cells.

Here’s the result:

There’s one situation where this feature is especially handy: that’s when I need to see all instances in one column of items that match a single characteristic in another column. For instance, “I want to see all the pie orders from customers who have ordered Red Velvet Cheesecake.”

I could do this operation with a Countif formula like…

=COUNTIFS([Name],[@Name],[Pie],"Red velvet cheesecake")>0

and then filter to all the rows that return TRUE.

But this new routine allows me to do this without helper columns.

Here’s How

First, select “Red Velvet Cheesecake,” then right-click and choose the regular old Filter > Filter by Selected Cell’s Value. You’ll see all the rows with that value.

Next, select all the customers and run the “Filter by All Selected Values” code. This will filter the Customers column by only the three currently visible names. This is the part that I don’t see how to do easily without this code.

Finally, clear the filter from “Pies” that you set in the first step. The result will match that above with the COUNTIFS formula.

A Brief Commercial Announcement

If you’re wondering how I got those things in my context menu see the MenuRighter page.

The Code

The code I wrote works for tables and contiguous areas.

Sub Filter_By_All_Selected_Values()

Dim FilterValues() As String
Dim i As Long, j As Long
Dim ColNumberInFilterRange As Long
Dim FilterRange As Excel.Range
Dim InTable As Boolean
Dim CollUniqueValues As Collection
Const MAX_FILTER_CELLS As Long = 10000

'Make sure we don't select cells hidden by filter
With Selection.SpecialCells(xlCellTypeVisible)
If Union(ActiveCell.EntireColumn, .EntireColumn).Address <> ActiveCell.EntireColumn.Address Then
MsgBox "Only select from one column"
Exit Sub
End If
If .Areas.Count = 1 And .Rows.Count = 1 Then
MsgBox "Select more than one cell"
Exit Sub
End If
If .Cells.Count > MAX_FILTER_CELLS Then
MsgBox "Cannot select more than " & MAX_FILTER_CELLS & " cells."
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

'Try to add every selected value to a collection - only unique values will succeed
Set CollUniqueValues = New Collection
For i = 1 To .Areas.Count
For j = 1 To .Areas(i).Cells.Count
On Error Resume Next
CollUniqueValues.Add .Areas(i).Cells(j).Text, .Areas(i).Cells(j).Text
On Error GoTo 0
Next j
Next i
'Transfer the collection to an array for the AutoFilter function
ReDim Preserve 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

How it Works

The first part of the code consists of a few checks to make sure the selection is more than one cell, is only in one column, and that you haven’t selected a huge number of cells. This last one is controlled by the MAX_FILTER_CELLS constant. I set it at 10,000, a number that ran with no noticeable delay.

After that the code checks whether the ActiveCell is in a table or not. In either case, it assigns the range to be filtered to the FilterRange variable. It then checks that all the currently selected cells reside within either the same table or contiguous area.

The code uses our old friend the “get unique items using a collection” trick. It then applies a filter to the FilterRange.

The trickiest part of the code was defining the conditions that require an Exit Sub, such as selecting in multiple columns. That’s mainly because something like “Selection.Columns.Count” only returns the count of columns in the first Area of a selection. The same thing applies to the Selection’s CurrentRegion. I got around these limitations by using Union and the fact that Selection.Address returns the address for all areas in the the selection.

Download

Here’s a workbook with the code and the pie orders table so you can easily try the filtering I did above.

Filter by Selected Cell’s Value – Bug With Blanks

Posted on April 30, 2015 by Doug Glancy

I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.

The Issue

In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.

Testing

I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.

How to Replicate It

Create a table like this one. It can actually be just one column:

Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.

You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:

Workarounds

The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.

A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.

So, Did Everybody Else Already Know This?

I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.

I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.

Autofilter VBA Operator Parameters

Posted on February 22, 2015 by Doug Glancy

The other day I was flipping back and forth between two tables with related sets of data and comparing the rows for one person in each set. I do this kind of thing all the time and often end up filtering each table to the same person, location, date, or whatever, in order to compare their rows more easily. I thought “Wouldn’t it be great to write a little utility that filters a field in one table based on the same criteria as a filter in another table?” Like many questions that begin with “Wouldn’t it be great to write a little utility” this one led me on a voyage of discovery. The utility isn’t finished, but I know a lot more about Autofilter VBA operator parameters.

My biggest discovery is that the parameters used in the Range.Autofilter method to set a filter don’t always match the properties of the Filter object used to read the criteria. For instance, you filter on a cell’s color by passing its RGB value to Criteria1. But when reading the RGB value of a Filter object, Criteria1 suddenly has a Color property. More about this and some other such wrinkles as we go along.

I also realized there are a bunch of xlDynamicFilter settings – like averages and dates – that all use the same operator and are specified by setting the Criteria1 argument.

I also noticed a typo in the xlFilterAllDatesInPeriodFebruray operator. I wonder if anybody has ever even used it?

The Basics – Setting a Filter in VBA

To review the basics of setting Autofilters in VBA you can peruse the skimpy help page. To add a bit to that, let’s return to one of my favorite tables:

Its columns have several characteristices on which you can filter, including text, dates and colors. Here it’s filtered to names that contain “A”, pies with the word “Berry”, dates in the current month and puke-green:

To filter the pies to just berry you have to load their names into an array and set Criteria1 and Operator like:

Sub PieTableFilters()
Dim lo As Excel.ListObject
Dim BerryPies As Variant

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
BerryPies = Array("Strawberry", "Blueberry", "Razzleberry", "Boysenberry")
lo.DataBodyRange.AutoFilter field:=2, _
Criteria1:=BerryPies, Operator:=xlFilterValues
End Sub

Discovery #1 – An xlFilterValues Array Set With Two Values Becomes an xlOR Operator:
To read the filter created by the code above, I’d still use the xlFilterValues operator and read the array from Criteria1. However, if the column was filtered to only two types of pies, I’d use the xlOR operator and Criteria1 and Criteria2. This is unchanged since Johh Walkenbach posted about Stephen Bullen’s function to read filters settings for Excel 2003.

Similarly, if I set a filter with a one-element array, e.g., only one type of pie, and then read the filter operator it returns a 0.

The Basics, Continued – Setting a Color Filter

Here’s how to set the filter for the last column. It’s based on cell color. Note that it works for Conditional Formatting colors as well as regular fill colors:

lo.DataBodyRange.AutoFilter field:=4, _
Criteria1:=8643294, Operator:=xlFilterCellColor

Here 8643294 is the RGB value of that greenish color. In order to determine that RGB in code, you need to use something like:

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
Debug.Print lo.AutoFilter.Filters(4).Criteria1.Color

Discovery #2 – The Filter.Criteria1 Property Sometimes has Sub-Properties, Like Color:
Note that the RGB isn’t in Criteria1 – it’s in Criteria1.Color, a property I only discovered by digging around in the Locals window:

Also note that there are a bunch of other properties there, like Pattern, etc.

Further, if a column is filtered by conditional formatting icon (yes you can do that) using the xlFilterIcon(10) parameter then Criteria1 contains an Icon property. This property is numbered 1 to 4 (I think) and relates to the position of the icon in its group.

Discovery #3: xlDynamicFilter and Its Many Settings
The xlFilterDynamic operator, enum 11, is a broad category of settings. You narrow them down in Criteria1. So, for instance, you can filter a column to the current month like:

lo.DataBodyRange.AutoFilter field:=3, _
Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
Operator:=xlFilterDynamic

This is a good time to mention the chart below that contains all of these xlDynamicFilter operators and their enums. Looking at it you’ll note that in addition to a whole lot of date filters (all of which appear in Excel’s front end as well) there’s also the Above Average and Below Average filters.

Discovery #4: Top 10 Settings Are Weird

To set a Top 10 item in VBA you do something like this:

lo.DataBodyRange.AutoFilter field:=4, _
Criteria1:=3, Operator:=xlTop10Items

Here, I’ve actually filtered column 4 to the top three items (As stated in Help, Criteria1 contains the actual number of items to filter). If I look at the filter settings for that column in the table I’ll see that Number Filters is checked and the dialog shows “Top 10.” That makes sense.

However if we look at the locals window right after the line above is executed, we see that the number 3 which we coded in Criteria1 is replaced by a greater than formula:

Further, if I then wanted to apply this filter to another column based only on what I’m able to read in VBA, I’d have to change it to:

'ValuePulledFromAnotherFilter contains ">=230"
lo.DataBodyRange.AutoFilter field:=4, _
Criteria1:=ValuePulledFromAnotherFilter

Note that I don’t use an operator at all. We could also use xlFilterValues or xlOr. Using nothing best reflects what shows in the locals window, where the operator changes to 0 after the code above is executed.

The Chart

Below is a chart, contained in a OneDrive usable and downloadable workbook, that summarizes much of the above. It includes all the possible Autofilter VBA Operator parameters, as well as all the sub-parameters for the xlFilterDynamic operator.

Discovery #5: February is the Hardest Month (to spell)

I like to automate things as much as possible, so generating the list of xlFilterDynamic properties was kind of a pain. To get the list of constants from the Object Browser I had to copy and paste one by one into Excel, where I cobbled together a line of VBA for each one, like:

Debug.Print “xlFilterAboveAverage: ” & xlFilterAboveAverage

Of course I saw I didn’t need to paste all the month operators into Excel since I could just drag “January” down eleven more cells and append the generated month names. Minutes saved!

But when I ran the code it choked on February. It was then I noticed that its constant is misspelled.

Two More Tips

1. Use Enum Numbers, not Constants: This misspelling of February points out a good practice when using any set of enums: use the number, not the constants. For example, use 8, not xlFilterValues. This helps in case the constants are changed in future Excel versions, and with international compatibility, late binding and, at least in this case, spelling errors. Of course it’s not nearly as readable and you have to figure out the enums.

2. The Locals Window is Your Friend: I don’t use the Locals window very much, but in working with these Autofilter settings, it was hugely helpful. It revealed things that I don’t think I’d have figured out any other way.

Conclusion – Building a Utility

Armed with my newfound knowledge, I could come close to building a utility that copies filters from one table to another, or one like the Walkenbach/Bullen filter lister linked at the beginning of this post. In most cases I could just transfer the filter by using the source Operator and Criteria1 (and Criteria2 where applicable)

But as we’ve seen, there’s a couple of filter types where the operator and/or criteria change. The worst of these is a Top 10 filter. My first thought was to count the number of unfiltered cells, but if the column has duplicates, e.g., your top value appears three times, that won’t always be accurate. In addition, other columns could be filtered which could also throw off the count.

Transferring a color scale filter would be even trickier as it’s very possible the same color wouldn’t exist in the second table.

Whew! Long post! Leave a comment if you got this far and let me know what you think.