Customize excel filter

Question

Hi, I've created a table in excel with a multiple-selection column and I'm trying to filter through the column according to specific values. Is it possible to customize a filter and only have certain values appear in the drop-down filter for that column? Or, if not, is it possible to change the order of the values in the filter? Currently the filter appears in alphabetical order but I want it to appear according to the order the values are placed in the column ex. the value in cell A1, A2, A3, etc.

Answer 1

You could add a data validation list to another cell, and use that cell and the the worksheet's change event to apply the filter to the data set.

For this technique to work, you need to shift your table down on the sheet so that the control cell stays visible.

If your table starts in A3 with headers in row 3 and you want to filter based on column A, then add the Data Validation list to cell A1 with the list of values in the order that you want, then:

1. Copy this code.
2. Right-Click the sheet tab of interest.
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes as needed

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then 

    With Range("A3").CurrentRegion 

        .AutoFilter Field:=1, Criteria1:=Target.Value 

    End With 

End If 

End Sub

Answer 2

Change

Criteria1:=Target.Value

to

Criteria1:="=*" & Target.Value & "*", Operator:=xlAnd

Answer 3

Hi, Thank you for your help. Just one final question- is it possible to change the code so that all cells with the selected value appear? For example, if some cells contain only A, others contain A and B and I select A in the filter, I want the filter to show all cells that include A, not just cells that only have A.

Answer 4

Here is a working example. I also changed the code so that if you clear F1 then all the data is shown. I set up the list to only show two of the four possible values in column F. Note that if column F is not the first column of the table, the "1" in the code needs to be changed to reflect column F's position in the table.

Answer 5

Hi, thank you that seems to solve my problem. However, I can't seem to be able to filter through the column. I put all the values I want to filter through in a cell above my table, but when I select a value nothing shows up. It just shows a blank table because none of the cells match with the selected value. I used this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$1" Then 

    With Range("F3").CurrentRegion 

        .AutoFilter Field:=1, Criteria1:=Target.Value 

    End With 

End If 

End Sub

Do you have any idea what the problem might be?