Creating DropDown by ComboBox1 and Filter the Desired Column

[Shazir]

I want to filter the column via ComboBox1 where ComboBox1 will load the unique values and upon selecting one of them will filter the column data accordingly.

I have tried at my end to make this code work but could not.

Any help will be highly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 Dim lastrow As Long
 
 With Sheets("Sheet1").Range("I15:I" & lastrow)
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Sheets("Sheet1").ComboBox1.List = Application.Transpose(.keys)
End With
 
 lastrow = Cells(Rows.Count, "I").End(xlUp).Row
 
 With Me
 If Not Intersect(Target, .Range("I1")) Is Nothing Then
 If Target.Value <> "" Then
 .AutoFilterMode = False
.Range("I15:I" & lastrow).AutoFilter field:=1, Criteria1:=Target.Value
 End If
 End If
 End With

 

[Shazir]

Sir, filter does not work. Everything is working fine ComboBox2 is having an issue.

ComboBox1 and Filter the Desired Column - Shazir.xlsm

drive.google.com

 

[Akuini]

Okay, I'll check it out tomorrow. It's almost midnight in my time zone.

 

[Shazir]

Sure.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Filtering Data for Each Column by ComboBox - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Shazir]

Apologize, for not mentioning the link.

I updated in other forum

 

[Akuini]

You right, there's a flaw in the code.
For ComboBox_Change() event the code should be like this:

Private Sub ComboBox1_Change()
Dim x, d As Object
With ComboBox1
If .Value <> "" Then
    Set d = CreateObject("scripting.dictionary")
    For Each x In vList
        If LCase(x) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
          d(x) = Empty
        End If
    Next
       .List = d.Keys
       .DropDown
         Sheets(sList).AutoFilterMode = False
         rngX.AutoFilter field:=1, Criteria1:=ComboBox1.Value
    Else
        .List = vList
    End If
End With
End Sub

So replace code in Private Sub ComboBox1_Change() and also in Private Sub ComboBox2_Change() accordingly.
And this should be applied whether you have only 1 combobox (as your first example) or multiple combobox (as your second example)

 

[Shazir]

I have tried as you said but its not working even after changing it. can you please ComboBox1 and Filter the Desired Column - Shazir.xlsm look at this sheet once that why 2nd Combobox is not working.