During one of my nerdy adventures, I came across the need to selectively hide AutoFilter Dropdowns. That is to say, apply an AutoFilter to a table, but only let the user see and interact with one or two of the dropdowns. Here's the deal:
The Problem:
I started with this table, which the client wanted to filter by Month and Primary Campaign.
Applying a standard AutoFilter will show dropdowns on all columns - even those between the ones I need.
We didn't want the end-users of this report to filter by sales or the visual arrow indicator.
So I tried to apply AutoFilters with only the columns I need selected. No love from Excel.
So Excel won't let me selectively apply AutoFilters, and it looks as though there is no way to hide the dropdowns via the user interface.
It didn't look good.
The Simple Fix:
After a little research, I found this property in the Object model (VisibleDropDown). That is, the AutoFilter object has a poperty called VisibleDropDown. Setting this to False will hide the dropdown.
For example, this little diddy will hide the second dropdown in the given AutoFilter:
Range("A2:E2").AutoFilter Field:=2, VisibleDropDown:=False
In my case, I want to only show the dropdowns for columns 1 and 5. So here is what I used:
Sub HideAutoFilterDropdowns()
With Range("A2:E2")
.AutoFilter Field:=1, VisibleDropDown:=True
.AutoFilter Field:=2, VisibleDropDown:=False
.AutoFilter Field:=3, VisibleDropDown:=False
.AutoFilter Field:=4, VisibleDropDown:=False
.AutoFilter Field:=5, VisibleDropDown:=True
End With
End Sub
Voilà! Everything works as it normally would with a standard AutoFilter setup - only, I've hidden a few of the dropdowns.
The Unnecessarily Complex Fix:
Finally, what kind of nerd would I be if I didn't provide an unnecessarily complex alternative that allows you to loop through the columns.
Actually, this piece of code would come in handy if you wanted to selective hide dropdowns across many many columns.
-
Sub HideAutoFilter()
-
Dim i As Integer
-
Dim MyRange As Range
-
Dim ValidFilters As Variant
-
-
Set MyRange = Range("A2:Z2") '<<ValidFilters = Array(1, 5, 7, 15, 20, 23) '<<
-
-
'Start with hidden dropdowns
-
For i = 1 To MyRange.Columns.Count
-
MyRange.AutoFilter Field:=i, VisibleDropDown:=False
-
Next i
-
-
'Now unhide only the one in your ValidFilters array.
-
For i = 0 To UBound(ValidFilters)
-
MyRange.AutoFilter Field:=ValidFilters(i), VisibleDropDown:=True
-
Next i
-
End Sub
How's the performance of this technique accross many columns?
Alexj: Lightning fast. The same performance you get by simply applying the AutoFilter.
Couple things. First, make sure if a column is not listed that you make the dropdown visible.
Second and more important, in your example if i=2, instr() is >0 because 20 is in the list. Thus 2 will not be hidden, even though 2 itself is not in the list. Don't be lazy, turn ValidFilters into an array using Split, then loop through the elements to check for matches.
@Jon,
You can still use InStr instead of Splitting the text and then looping it; just change ValidFilters to this...
ValidFilters = "*1*5*7*15*20*23*" 'asterisk delimited list
and then use this If..Then statement to do the test...
If InStr(ValidFilters, "*" & i & "*") = 0 Then
Damn you Peltier....you got me. I adjusted the code to use an array.
Rick: Great idea - just too late for me to save face.
Setting an array of auth app.username allows you to also leave yourself a back door with the following:
If application.uername dev then
HideAutoFilter
End If
that way only cust will have restricted filters.
I wanted to check the possibility of showing the filter arrows with a bit of flexibility.
The code below maybe a bit crude, but it does the job.
A couple of changes were made in the worksheet:
1. The Header Row is not Row2, but Row4
2. I have provided for TRUE or FALSE to be entered in Row2
Based on the value in Row2 for each column, the AutoFilter arrows will be Visible or Hidden.
The user can edit these values to show / hide the drop-down arrows, and re-run the macro.
Khushnood
I wanted to see if you could still filter those columns using ALT+Arrowdown and found something surprising - you can't filter those columns but it turns what was the filtering list into a data validation list for that cell! So, lock those cells so they can't be selected, maybe?
Wonderful - just what I needed. Thanks, M