Filter Table using VBA Excel

Question

I have two worksheets. In worksheets("Dashboard") I have a dropdown list based on a table in worksheets("yearly View"). I am trying to filter this table based on the value in the drop down list in worksheets("Dashboard").

The code I have created is failing.

The drop down list in worksheets("Dashboard") is location in Range("AL1"). The column to be filtered is on worksheets("Yearly View").Range("P3").

Please see my code below:

Sub Filter_Table()
     Worksheets("Yearly View").Range("P3").Select
     Worksheets("Yearly View").Range("$P$3:$AR$24").AutoFilter Field:=1, Criteria1:= _
     Worksheets("Dashboard").Range("AL1")
     ActiveSheet.Range("$P$3:$AR$24").AutoFilter Field:=1
End Sub

Is this dropdown you mention from Data Validation or a Form listbox or even an ActiveX listbox? — Xabier
– Xabier, Commented Dec 13, 2017 at 17:23
Remove the last line of code. You basically filter than unfilter again with that last line. Also, the first line can be removed as well. it's erroneous. — Scott Holtzman
– Scott Holtzman, Commented Dec 13, 2017 at 17:26

Xabier · Accepted Answer · 2017-12-13 17:37:49Z

2

If you put the code below under the Worksheet_Change on the Sheet "worksheets("Dashboard")" then the value of the listbox will be used to filter the Field 3 in this case on the Sheet "Yearly View":

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AL$1" Then
Worksheets("Yearly View").ListObjects("YourTableName").Range.AutoFilter Field:=3, Criteria1:=Target.Value 'change the name of your table here. and the field value to the column number to be filtered on your table
End If
End Sub

answered Dec 13, 2017 at 17:37

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter Table using VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related