PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How can I delete duplicate item from listbox in word vba???-VBForums
Results 1 to 8 of 8

Thread: How can I delete duplicate item from listbox in word vba???

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2019
    Posts
    1

    Cool How can I delete duplicate item from listbox in word vba???

    I have got a code to delete duplicate item from listbox in word vba??? This code shows the problem of array.


    Code:
    Dim intI As Long
    Dim intJ As Long
    Set pobjlb = ListBox1
        With pobjlb
            For intI = 0 To .ListCount - 1
            For intJ = .ListCount To (intI + 1) Step -1
            If .List(intJ) = .List(intI) Then
               .RemoveItem intJ
            End If
            Next
            Next

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,865

    Re: How can I delete duplicate item from listbox in word vba???

    I do not use listboxes in VBA but it looks like your problem is the outter loop. My guess would be that you are getting an error related to index out of bounds and/or it is removing the wrong item after one item is removed.

    The solution would be to change the outter loop to count backwards from the end like the inner loop does.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,819

    Re: How can I delete duplicate item from listbox in word vba???

    Sort the Listbox, loop bottom up, compare current entry with Entry one up, if equal, remove current entry
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    Hyperactive Member
    Join Date
    Feb 2019
    Posts
    305

    Re: How can I delete duplicate item from listbox in word vba???

    Besides what others suggested, beware that in For loops, VB saves the "To" variable in a temporary variable, and uses that. In the example below, VB saves the contents of the "c" variable. If you were using a function call, this would prevent VB from calling the function 10 times. So if I change "c" in the loop, the loop will still perform 10 times:

    VB Code:
    1. Private Sub Form_Load()
    2.     Dim i As Long
    3.     Dim c As Long
    4.    
    5.     c = 10
    6.     For i = 1 To c
    7.         c = 3 ' Limiting to 3 Loops doesn't work in For loops
    8.         Debug.Print i
    9.     Next
    10.    
    11.     c = 1
    12.     For i = 10 To c Step -1
    13.         c = 3 ' Limiting to 3 Loops doesn't work in For loops
    14.         Debug.Print i
    15.     Next
    16. End Sub

    This prints:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    10
    9
    8
    7
    6
    5
    4
    3
    2
    1

    The solution is to use a Do While loop if the count changes in the middle of the loop, like removing elements from an array or a list. Example:

    Code:
    i = 1
    Do While i <= 10
    
        i = i + 1
    Loop
    Last edited by qvb6; May 12th, 2019 at 04:46 PM.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,247

    Re: How can I delete duplicate item from listbox in word vba???

    Moved to Office Development. VBA questions are better off in here.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: How can I delete duplicate item from listbox in word vba???

    yet another option, for a sinlge column listbox, would be to add all the listbox items into a collection (which will not allow duplicates), clear the listbox then re-add the items from the collection

    with a multi column listbox you would also need to store any column data possibly in an array using the index of the collection to retrieve the column data
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,863

    Re: How can I delete duplicate item from listbox in word vba???

    Hi

    you could use the API

    Code:
    Option Explicit
    ' benötigte API-Deklarationen
    Private Declare Function SendMessageByString Lib "user32" _
      Alias "SendMessageA" ( _
      ByVal hwnd As Long, _
      ByVal wMsg As Long, _
      ByVal wParam As Long, _
      ByVal lParam As String) As Long
     
    Private Const LB_FINDSTRINGEXACT = &H1A2
    
    Public Sub RemoveDbl(DestLB As ListBox)
      Dim I As Long
     
      With DestLB
        For I = .ListCount - 1 To 0 Step -1
          If SendMessageByString(.hwnd, LB_FINDSTRINGEXACT, I, .List(I)) <> I Then
            .RemoveItem I
          End If
        Next I
      End With
    End Sub
    
    Private Sub Command1_Click()
    Call RemoveDbl(List1)
    End Sub
    or
    Code:
    Private Sub Command2_Click()
    Dim intI As Long
    Dim intJ As Long
        With List1
        .Visible = False
            For intI = .ListCount - 2 To 0 Step -1
             For intJ = .ListCount - 1 To intI + 1 Step -1
               If .List(intJ) = .List(intI) Then .RemoveItem intJ
            Next intJ
        Next intI
            .Visible = True
        End With
    End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,819

    Re: How can I delete duplicate item from listbox in word vba???

    Quote Originally Posted by westconn1 View Post
    yet another option, for a sinlge column listbox, would be to add all the listbox items into a collection (which will not allow duplicates), clear the listbox then re-add the items from the collection

    with a multi column listbox you would also need to store any column data possibly in an array using the index of the collection to retrieve the column data
    How do you figure that?
    Only if you provide a Key for the AddItem-Method
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width