Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Remove Duplicates between two Dynamic Listboxes

  1. #1

    Remove Duplicates between two Dynamic Listboxes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    First time here, so big hi everyone!

    I've been pulling my hair for hours and was wondering if anyone can help me before i get bald

    Im trying to remove all items from listbox1 that are in listbox2. All the code is working find until it needs to remove the item in an arry. Thats the part where im getting an invalid argument error. Can someone please help me with this?

    Code:
    Private Sub CompareListboxes(lsb1 As MSForms.ListBox, lsb2 As MSForms.ListBox)
    
    
    Dim i As Long
    Dim x As Integer, Tabl()
    
    
        ReDim Tabl(0)
    
    
        For i = 0 To lsb1.ListCount - 1
             x = x + 1
            ReDim Preserve Tabl(x)
            Tabl(x) = lsb1.List(i)
        Next i
        For i = 0 To lsb2.ListCount - 1
            If IsNumeric(Application.Match(lsb2.List(i), Tabl, 0)) Then
                lsb1.RemoveItem lsb2.List(i) ' <----- Error here
            End If
        Next i
    End Sub

  2. #2
    You delete by index not value, and you have to work up the list not down, else the pointer screws up.

    Code:
    Private Sub CompareListboxes(lsb1 As MSForms.ListBox, lsb2 As MSForms.ListBox)Dim Tabl As Variant
    Dim x As Long
    Dim i As Long
    
    
        Tabl = Application.Transpose(lsb1.List)
        
        For i = lsb2.ListCount - 1 To 0 Step -1
        
            x = 0
            On Error Resume Next
            x = Application.Match(lsb2.List(i), Tabl, 0)
            On Error GoTo 0
            If x > 0 Then lsb1.RemoveItem x - 1
        Next i
    End Sub

  3. #3
    Ahh! I see what you mean! The code seems good, it just gives a type mismatch for Tabl and i couldnt find a workable type. Can you help me with that please?

  4. #4
    I think that is just a forum issue concatenating lines

    Code:
    Private Sub CompareListboxes(lsb1 As MSForms.ListBox, lsb2 As MSForms.ListBox)
    Dim Tabl As Variant
    Dim x As Long
    Dim i As Long
    
        Tabl = Application.Transpose(lsb1.List)
        
        For i = lsb2.ListCount - 1 To 0 Step -1
        
            x = 0
            On Error Resume Next
            x = Application.Match(lsb2.List(i), Tabl, 0)
            On Error GoTo 0
            If x > 0 Then lsb1.RemoveItem x - 1
        Next i
    End Sub

  5. #5
    No no, i fixed the declaration. Its in the formula itself i think

  6. #6
    There is no formula, it is code. What line throws the error?

  7. #7
    I meant code... It gives a Type mismatch error in

    Tabl = Application.Transpose(lsb1.List)

  8. #8
    Worked for me. Can you post the workbook?

  9. #9
    Sure. The code is located in the TextBoxChange module. It is mainly working with many dynamic multipages and textboxes + listboxes. Let me know if i can clarify anyhting.

    File: Cost DB.xlsm

    Edit: Forgot to mention, the textbox is located when running the macro in Filters>All Filters> Any of the filters text box ( first need to place at least 1 to the filters from 1 listbox to the other for the textbox to work)
    Last edited by JustLukas; 2014-06-11 at 11:42 AM.

  10. #10
    I don't want to spend my time wading through all of your code to try and figure out what gets called where, just tell me what I need to click/enter/do to test this function.

Page 1 of 2 1 2 LastLast

Posting Permissions

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