Filtering Using A Dropdown List

[tony68]

Hi completely new to VBA but have managed to create a filter via a dropdown list using code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If Range("B2") = "All" Then
Range("A5").AutoFilter
Else
Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2")
End If
End If
End Sub

What I want to do now is add a second filter from a different dropdown list - is this possible?

 

[Trevor G]

Welcome to the Board tony68,

If you notice in your code you have Criteria1 then you should be able to add Criteria2.

Simply advice is record a macro to apply two filters and then stop the recording, check the code in the recording and it will give you the solution you want to apply.

 

[tony68]

Thanks Trevor G I have tried recording a Macro but does not help me - possibly as I am not sure what exactly it is I am doing!!

 

[Trevor G]

Ok then Tony please explain the second condition (criteria) and see if we can help.

 

[tony68]

OK what I have is a large spreadsheet and I want to run a VBA code that filters out by Area Manager and the by Office Type. Where it shows Man A in row 1 cell 2 is a drop down list as is row 2 cell 2. So in essence I want to be able to select the appropriate manager from the drop down and then filter further by office type. There is a 3rd thing I want to do but lets try and crack this first. Hope makes sense.

	Man A
	Local Depot
Row	Labels	Office Type	Postcode	Area Manager
2​	Newport West DO	Delivery Office	NP	Man A
4​	Chesterfield DO	Delivery Office	S	Man B
6​	Edinburgh LD	Local Depot	EH	Man C
21​	Gloucester North DO	Delivery Office	GL	Man A
23​	Gloucester South DO	Delivery Office	GL	Man B
36​	Kirkintilloch DO	Delivery Office	G	Man C
41​	London North West LD	Local Depot	W	Man A
57​	Bolton North DO	Delivery Office	BL	Man B
58​	Brinklow DO	Delivery Office	MK	Man C
59​	Bletchley DO	Delivery Office	MK	Man A
75​	Winton DO	Delivery Office	BH	Man B
77​	Nottingham LD	Local Depot	NG	Man C
80​	West Thurrock HUB	Hub	RM	Man A

 

[Trevor G]

Have you thought of using Data Tables with Slicers that would provide you with enough options without code. In the example image there is a table (Your data example) I have highlighted it and then set it to be a Data Table (Insert Tab and Table on the left). Then In the Design tab that becomes available I have set to Slicers and you then just filter from them.

 

[tony68]

No had not thought of that but something similar but was trying to avoid having a lot of "buttons" - full sheet has 20 area managers and 13 office types. Will give it a go though and see how it looks. Thanks for the tip.

 

[tony68]

Hi Trevor yeah that looks good - probably looks more professional than a simple drop down list. One more question if I may - when I filter how do I get it to show only the topX sites?

 

[Trevor G]

OK how would you get the top X sites now, is there a number system or something else, and what classes a site to be at the top?

It maybe a pivot table helps you

 

[tony68]

There is an average per meter squared column so it would be the top X sites in that column - so if I filter on manager I will get say 300 building then if I filter on building type I will get say 105 buildings which is too many so what I ideally want to do is when selecting the filter by building type it only returns the top x. Hope that makes sense.