I have a worksheet that has a column with comma separated values. which gets broken up and added to a multiselect listbox as individual items. these items are categories so there are repeated items and such. ie.
cells:
a1 = cat, dog, mouse
a2 cat, dog
a3 mouse
a4 dog mouse
listbox: all
cat
dog
mouse
what i'm trying to do is have it hide all rows except the rows which contain the items selected, but it is only showing the first item selected from the list instead of all of them and only if it isn't one of the cells that has more than one item in it.
any help would be much appreciated.
Thanks,
Chris
'Creates Catalog List
Set dic = CreateObject("Scripting.Dictionary")
For Each r In Range(CatCol & "2", Range(CatCol & Rows.Count).End(xlUp))
If r.Value <> "" Then
For Each e In Split(r.Value, ",")
dic(StrConv(Trim$(e), 3)) = Empty
Next
End If
Next
Me.ListBox1.List = dic.keys
'Sorts ListBox List
With Me.ListBox1
For j = 0 To ListBox1.ListCount - 2
For i = 0 To ListBox1.ListCount - 2
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
End If
Next i
Next j
End With
'Adds a filter for all items
ListBox1.AddItem ("All Items"), 0
End If
ActiveSheet.Cells.EntireRow.Hidden = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
'Get Listbox Data
Dim i As Integer
Dim x As Variant
ReDim x(0)
For i = 0 To ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
x(UBound(x)) = Me.ListBox1.List(i)
ReDim Preserve x(UBound(x) + 1)
End If
Next i
'Skips filtering if "All Items" are selected
If ListSelect <> "All Items" And ListBox1.Visible <> False Then
'Filters the sheet based on selection
Set rng = Range(CatCol & "1:" & CatCol & LastRow)
rng.AutoFilter Field:=1, Criteria1:=x, Operator:=xlFilterValues, visibledropdown:=False
Application.ScreenUpdating = True
End If
Bookmarks