Filter data by multiple independent drop-downs for multiple columns

KatRum

New Member
Joined
Nov 1, 2018
Messages
11
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


DYDZEAEBECEDEEEF
Lot 1Lot 2Lot 3Lot 4Lot 5Lot 6Lot 7Lot 8
Company 1Apples
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 2Apples
Kiwis
Chairs
Tables
Teapots
Coffee
Mouse
Keyboard
Mobile
Kat
Gemma
Plumbers
Scaffolding
Electricians
Potato
Broccoli
Parsnip
Company 3Oranges Tea
Coffee
Teapots
Mobile
Sam
Gemma
Oak
Parsnip
Broccoli
Company 4Bananas
Oranges
Kiwis
Sofas Keyboard
Desktop
Sam
Mike
Kat
Elm
Oak
Willow
Scaffolding
Electricians
Broccoli
Company 5Bananas
Apples
Tables
Chairs
CoffeeLaptop
Mouse
Mike
Sam
Steve
Elm
Oak
Willow
Builder
Brickie
Scaffolding
Potato
Carrot
Company 6Melons
Bananas
CupboardsCoffee
Cappucino
Steve
Sam
Elm
Carrot

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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,275
Code:
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

New Member
Joined
Nov 1, 2018
Messages
11
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,275
"Doesn't work" is hardy descriptive and difficult to diagnose. Could you give it another shot?
 

KatRum

New Member
Joined
Nov 1, 2018
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,275
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

New Member
Joined
Nov 1, 2018
Messages
11
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**
 

Watch MrExcel Video

Forum statistics

Threads
1,108,652
Messages
5,524,106
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top