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











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks