Autofilter data based on listbox selections

Greetings.

Iam trying to auto filter a data range based on criteria passed from a list box selections.I want the auto filter to be filtering column 2 of the data range based on ALL the items SELECTED from a multi-select list box (named listbox2) at the click of a button. Here is what i currently have:

Sub Cmd1_Click()


 Application.ScreenUpdating = False
 For i = 0 To ListBox2.ListCount - 1
    If Me.ListBox2.Selected(i) Then
       Range("A3:C600").AutoFilter Field:=2, Criteria1:=ListBox2.List(i), Operator:=xlFilterValues
    End If
 Next i
 Application.ScreenUpdating = True


End Sub

It works, only that it filters the data by ONLY the last selected value of the list(i.e only one value item in the list). I want it to loop through ALL the selections, applying filter on column 2 based on EACH of the selections on the list box.

Thanks in anticipation.

Re: Autofilter data based on listbox selections

THANKS ! That worked PERFECTLY !!I also appreciate your quick response, the explanation of how it works and why my code failed.

I hope I wont be bothering you if i sweetened it further. How would it be if I had THREE multi-select Listboxes ?
i.e I want to provide the user a form interface where he can select any values from three listboxes and filter the same data range
selections in listbox1 will filter column 1, selections in listbox2 will filter column 2, and so on, up to listbox3. If selections are made on ALL the listboxes, then the data would represent values meeting ALL the selection criteria.

I dont know much about Arrays but am sure there is a way to Loop through all the Listbox selections.

Once again, thanks for the help.

Re: Autofilter data based on listbox selections

Thanks, actually I had tried earlier before seeking your help, only that my code filters out the ENTIRE data range i.e all the data is invisible. This is so even if selections are made in only one listbox. I am relatively new to VBA (barely 5 months),and I am not very good with arrays, so I hoped for some kind of a loop to loop through all the listboxes.

Anyway, this is what I had:

Private Sub Cmd1_Click()


Dim x, k, r As Variant
ReDim x(0)


 Application.ScreenUpdating = False
 For i = 0 To ListBox2.ListCount - 1
    If Me.ListBox2.Selected(i) Then 
     x(UBound(x)) = Me.ListBox2.List(i)
     ReDim Preserve x(UBound(x) + 1)
    End If
 Next i
 Range("A3:C600").AutoFilter Field:=2, Criteria1:=x, Operator:=xlFilterValues


ReDim r(0)


 For j = 0 To ListBox1.ListCount - 1
    If Me.ListBox1.Selected(j) Then
     r(UBound(r)) = Me.ListBox1.List(j)
     ReDim Preserve r(UBound(r) + 1)
    End If
 Next j
 Range("A3:C600").AutoFilter Field:=1, Criteria1:=r, Operator:=xlFilterValues


ReDim k(0)


 For s = 0 To ListBox3.ListCount - 1
    If Me.ListBox3.Selected(s) Then
     k(UBound(k)) = Me.ListBox3.List(s)
     ReDim Preserve k(UBound(k) + 1)
    End If
 Next k
 
 Range("A3:C600").AutoFilter Field:=3, Criteria1:=k, Operator:=xlFilterValues


 Application.ScreenUpdating = True
End Sub

Please direct me further. Thanks.

Re: Autofilter data based on listbox selections

THANKS ! That worked like MAGIC !!!!

That was more than I could have asked for.It blew me away, considering you even came up with an illustration.THANK YOU SIR !!

OH! and by the way...there's a final listbox...yes there is.... where the data is in Months (Jan to Dec) over row range (G3:R3). I want the code to be able to hide the month column for unselected items, i.e hide all other columns if the user selected Jan to April.

As always, I have given it a try, (based on the filter code for the other listboxes) but It doesn't do anything. Here's what I have...

Private Sub cmd1_Click()


Dim x() 
Dim i As Integer


ReDim x(0)


Application.ScreenUpdating = False


Range("G3:R3").Select


For i = 0 To ListBox4.ListCount - 1
    If Me.ListBox4.Selected(i) Then
        x(UBound(x)) = Me.ListBox4.List(i)
        ReDim Preserve x(UBound(x) + 1)
    End If
Next i


If UBound(x) <> 0 Then
    ReDim Preserve x(UBound(x) - 1)
    Columns("x").Select
    Selection.EntireColumn.Hidden = True    
End If
    
Application.ScreenUpdating = True
    
End Sub

I hope to add this as the final piece to the previous code. Please direct me further. Many THANKS.

Re: Autofilter data based on listbox selections

THANK YOU SIR !!!
That was very helpful and I can call it a wrap.I have also learnt a lot and keen on improving further.

Moreover...I hope you do not mind....:oops:

I had another tougher issue, related to the previous one. It pertains to a similar project and am completely stuck. This time, due to the data format, It would require a Treeview control.

The data format is as:

Beverages - Tea
- Coffee
- Lemonade
- Soda

Fruits - Mango
- Apple
- Orange

e.t.c

the categories are in column A while the items are in column B. I am not familiar with Treeview properties and events as Ive never used it. I want code to populate the data to the treeview from the two columns, with Column A values as the Parent and its corresponding values in Column B as the children. I believe it is possible using Arrays, or any other way, and thereafter apply the treeview selections as filters to the data range at the click of a button. I hope I'm clear.

I look forward to your guidance. Thank you for your valuable help thus far.

Actuarish

Re: Autofilter data based on listbox selections

Thank you for the links. I basically copy-pasted the code but it did not work the way I hoped. I will just post my issue in the forums and hope for the best. Thank you. Actuarish