Results 1 to 2 of 2

Comma delimited cells multiselect listbox filter

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2013
    Posts
    114

    Comma delimited cells multiselect listbox filter

    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
    Last edited by Code Flunkie; 07-08-2016 at 11:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Search/Filter w/ Multiselect ListBox
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2014, 10:16 AM
  2. [SOLVED] Multiple cells comma delimited to rows
    By CTRED in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-25-2014, 01:03 PM
  3. [SOLVED] For next loop multiselect activex listbox won't list items in seperate cells. Only 1st
    By Dabbler39 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-05-2013, 03:25 PM
  4. [SOLVED] Paste Only Visible Cells, Comma Delimited
    By ATLGator in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2013, 12:52 PM
  5. Use a Multiselect listbox to filter data
    By EoghanMBH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2012, 07:21 AM
  6. MultiSelect Listbox selections to cells
    By Borg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2009, 02:39 AM
  7. separating values in comma delimited cells
    By JChandler22 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2008, 06:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1