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

[TABLE="width: 729"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]DY[/TD]
[TD]DZ[/TD]
[TD]EA[/TD]
[TD]EB[/TD]
[TD]EC[/TD]
[TD]ED[/TD]
[TD]EE[/TD]
[TD]EF[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Lot 1[/TD]
[TD]Lot 2[/TD]
[TD]Lot 3[/TD]
[TD]Lot 4[/TD]
[TD]Lot 5[/TD]
[TD]Lot 6[/TD]
[TD]Lot 7[/TD]
[TD]Lot 8[/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD]Apples
Oranges
Kiwis[/TD]
[TD]Sofas
Armchairs
Tables
Chairs[/TD]
[TD]Cups
Teapots
[/TD]
[TD]Desktop
Laptop
Mouse
[/TD]
[TD]Kat
Sarah
Mike
Gemma
[/TD]
[TD]Willow
Oak
[/TD]
[TD]Scaffolding
Roofers[/TD]
[TD]Peas
Broccoli
Parsnip
Tomato
Potato[/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD]Apples
Kiwis[/TD]
[TD]Chairs
Tables[/TD]
[TD]Teapots
Coffee
[/TD]
[TD]Mouse
Keyboard
Mobile
[/TD]
[TD]Kat
Gemma
[/TD]
[TD] [/TD]
[TD]Plumbers
Scaffolding
Electricians[/TD]
[TD]Potato
Broccoli
Parsnip[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD]Oranges[/TD]
[TD] [/TD]
[TD]Tea
Coffee
Teapots
[/TD]
[TD]Mobile
[/TD]
[TD]Sam
Gemma
[/TD]
[TD]Oak
[/TD]
[TD] [/TD]
[TD]Parsnip
Broccoli[/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD]Bananas
Oranges
Kiwis[/TD]
[TD]Sofas[/TD]
[TD] [/TD]
[TD]Keyboard
Desktop
[/TD]
[TD]Sam
Mike
Kat
[/TD]
[TD]Elm
Oak
Willow
[/TD]
[TD]Scaffolding
Electricians[/TD]
[TD]Broccoli[/TD]
[/TR]
[TR]
[TD]Company 5[/TD]
[TD]Bananas
Apples[/TD]
[TD]Tables
Chairs[/TD]
[TD]Coffee[/TD]
[TD]Laptop
Mouse
[/TD]
[TD]Mike
Sam
Steve
[/TD]
[TD]Elm
Oak
Willow
[/TD]
[TD]Builder
Brickie
Scaffolding[/TD]
[TD]Potato
Carrot[/TD]
[/TR]
[TR]
[TD]Company 6[/TD]
[TD]Melons
Bananas[/TD]
[TD]Cupboards[/TD]
[TD]Coffee
Cappucino
[/TD]
[TD] [/TD]
[TD]Steve
Sam
[/TD]
[TD]Elm
[/TD]
[TD] [/TD]
[TD]Carrot[/TD]
[/TR]
</tbody>[/TABLE]

 

[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**