Posts by actuarish

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 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.

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.