The columns (based on column B,C,D) can be filtered as dependent with userform quickly.
The userform contains 3 textbox and 3 listbox. When any textbox is clicked ,the userform extends downwards later listbox that associated to textbox appears .
Data in column are listed as unique and are sorted alphabetic . With text boxes,value can be searched within the listbox with Ado Connection :
"....
Dim s As String, con As Object
Me.ListBox1.Clear
DoEvents
Application.ScreenUpdating = False
Set con = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No;"""
#Else
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=No;"""
#End If
s = "select distinct f2 from [Main$A3:D" & Range("D" & Rows.Count).End(xlUp).Row & "] where not isnull(f2)"
If TextBox1.Text <> "" Then s = s & " and f2 like '" & VBA.UCase(LCase(TextBox1.Text)) & "%'"
On Error GoTo hata
ListBox1.Column = con.Execute(s).getrows
Application.ScreenUpdating = True
"....
Dim s As String, con As Object
Me.ListBox1.Clear
DoEvents
Application.ScreenUpdating = False
Set con = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No;"""
#Else
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=No;"""
#End If
s = "select distinct f2 from [Main$A3:D" & Range("D" & Rows.Count).End(xlUp).Row & "] where not isnull(f2)"
If TextBox1.Text <> "" Then s = s & " and f2 like '" & VBA.UCase(LCase(TextBox1.Text)) & "%'"
On Error GoTo hata
ListBox1.Column = con.Execute(s).getrows
Application.ScreenUpdating = True
...
"
"
In addition, the filtering is done with "AutoFilter" method within the worksheet.
Filtering results can be copied to other sheet.
