Filter data by multiple independent drop-downs for multiple columns

[KatRum]

Hi All,

I received a really good response from here before and I am hoping someone can help me again. I browsed for an answer but it does not exist so far. I'll explain what I am trying to do and also what I have so far.

I have a large table with a lot of data. I have some dropdowns in B2:B9 which correspond with the data in columns DY:EF in the respective order. I would like the dropdowns to filter the data but only in the respective field. People will only use one dropdown at a time so they don't have to interact with each other. I try to demonstrate below:
I currently have the following which only works for one dropdown:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Dim lastRow As Long
Dim myTable As Range
lastRow = Cells(Rows.Count, "EG").End(xlUp).Row
Set myTable = Range("A13:EG" & lastRow)
myTable.AutoFilter field:=129, Criteria1:="=*" & Range("B2").Value & "*"
End Sub

	DY	DZ	EA	EB	EC	ED	EE	EF
	Lot 1	Lot 2	Lot 3	Lot 4	Lot 5	Lot 6	Lot 7	Lot 8
Company 1	Apples Oranges Kiwis	Sofas Armchairs Tables Chairs	Cups Teapots	Desktop Laptop Mouse	Kat Sarah Mike Gemma	Willow Oak	Scaffolding Roofers	Peas Broccoli Parsnip Tomato Potato
Company 2	Apples Kiwis	Chairs Tables	Teapots Coffee	Mouse Keyboard Mobile	Kat Gemma		Plumbers Scaffolding Electricians	Potato Broccoli Parsnip
Company 3	Oranges		Tea Coffee Teapots	Mobile	Sam Gemma	Oak		Parsnip Broccoli
Company 4	Bananas Oranges Kiwis	Sofas		Keyboard Desktop	Sam Mike Kat	Elm Oak Willow	Scaffolding Electricians	Broccoli
Company 5	Bananas Apples	Tables Chairs	Coffee	Laptop Mouse	Mike Sam Steve	Elm Oak Willow	Builder Brickie Scaffolding	Potato Carrot
Company 6	Melons Bananas	Cupboards	Coffee Cappucino		Steve Sam	Elm		Carrot

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:B9")) Is Nothing Then Exit Sub
    Dim lastRow   As Long
    Dim myTable   As Range
    lastRow = Cells(Rows.Count, "EG").End(xlUp).Row
    Set myTable = Range("A13:EG" & lastRow)
    myTable.AutoFilter 'clear previous filter
    myTable.AutoFilter Field:=127 + Target.Row, Criteria1:="=*" & Target.Value & "*"
End Sub

 

[KatRum]

Hi Alpha,

Thank you for coming back to me. I'm afraid the formula doesn't work for some reason. Could you give it another shot?

 

[AlphaFrog]

"Doesn't work" is hardy descriptive and difficult to diagnose. Could you give it another shot?

 

[KatRum]

Sorry I will give this another try. I don't get an error message which is great. The filter works for only the first column (129) which is linked with the B2 dropdown. B3-B9 which should be filtering Fields 130-136

 

[AlphaFrog]

In my testing, it works for all cells in B2:B9 and their respective fields 129-136
I don't know what else to tell you.

 

[KatRum]

In my testing, it works for all cells in B2:B9 and their respective fields 129-136
I don't know what else to tell you.

I apologise I was wrong and happy to admit it, I pasted it in the wrong place. It works and is doing a beautiful job. Thank you AlphaFrog, you **ROCK**