Data Filter Using Data Validation List

[psulions83]

Hello All,

I know I have done the below question before but for the life of me cannot remember and find the right answer.

I have a list in columns A-C. I have a dropdown in C1 with the unique value from column A. I was the list to filter based on what value I pick from the drop down. Can anyone help point me in the right direction?

Thanks!

 

[HighAndWilder]

Hello All,

I know I have done the below question before but for the life of me cannot remember and find the right answer.

I have a list in columns A-C. I have a dropdown in C1 with the unique value from column A. I was the list to filter based on what value I pick from the drop down. Can anyone help point me in the right direction?

Thanks!

This is one way of doing it.

The code goes in the worksheet code module.

Try it with this sheet and then adapt it to meet your needs.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count > 1 Then
    Exit Sub
  End If
  
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
  
  If Target = Range("C1") Then

    ' Clear Filter.
    If Target.Value = "Clear" Then
      On Error Resume Next
      ActiveSheet.ShowAllData
      On Error GoTo 0
    Else
  
      ' Apply Filter.
      With ActiveSheet.Range("A3").Resize(Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row - 2, 3)
        .AutoFilter field:=1, Criteria1:=Range("C1").Value
      End With
  
    End If
  
  End If
 
End Sub

Recent Files.xlsm
	A	B	C
1			Clear
2
3	Column 1	Column 2	Column 3
4	8	5	3
5	4	3	6
6	2	4	6
7	4	1	8
8	3	6	6
9	1	2	2
10	8	2	8
11	8	4	1
12	6	1	4
13	4	5	8
14	7	3	6
15	7	5	3
16	7	8	4
17	6	1	6
18	1	3	1
19	8	6	1
20	7	5	6
21	6	7	3
22	5	1	4
23	2	3	1
24	8	5	1
25	5	8	4
26	7	3	3
27	8	6	8
28	4	2	7
29	6	4	4
30	6	1	5
31	3	8	1
32	8	5	1
33	3	4	8
34	3	6	6
35	1	4	3
36	5	8	8
37	3	6	3
38	5	4	7
39	1	3	5
40	1	1	6
41	2	7	1
42	8	7	2
43	2	6	1
44	5	5	6
45	3	1	1
46	2	2	2
47	6	1	4
48	3	7	7
49	5	5	2
50	4	5	7
51	5	7	1
52	3	8	4
53	2	8	6
54	2	1	1
55	1	3	5
56	4	3	4
57	5	6	4
58	8	4	6
59	4	2	7
60	2	2	1
61	8	8	2
62	4	6	4
63	8	5	1
64	7	6	8
65	7	8	6
66	8	5	2
67	2	4	8
68	6	1	8
69	3	4	7
70	7	2	1
71	1	7	1
72	1	7	3
73	8	6	4
ToFilter

Cell Formulas
Range	Formula
A3:C3	A3	="Column " & COLUMN()

Cells with Data Validation
Cell	Allow	Criteria
C1	List	Clear,1,2,3,4,5,6,7,8

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in C1. The macro assumes you have headers in row 1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Range("A1").CurrentRegion.AutoFilter 1, Target.Value
End Sub