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
<colgroup><col><col span="8"></colgroup><tbody>
</tbody>
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>