+ Reply to Thread
Results 1 to 4 of 4

Remove duplicates out of combobox

Hybrid View

  1. #1
    Registered User
    Join Date
    10-05-2014
    Location
    nl
    MS-Off Ver
    8
    Posts
    13

    Unhappy Remove duplicates out of combobox

    Good evening,

    Question : i have on a form two comboboxes witch be filled with data from a sheet "Ritspecs"
    Furter i can input new data in the comboboxes that wil added in "Ritspecs"
    How can i prevent double data in the comboboxes ?




    HTML Code: 
    thanks in advance Rob

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Remove duplicates out of combobox

    Count the number of times you've already encountered the value in your progress up the column ... if count = 0 , on rows already processed then add the current rows value (NOTE: the counting is be performed by excels "CountIf" worksheet function).

    For i = lastrow To 1 Step -1
      If Application.WorksheetFunction.CountIf(.Range("A" & i + 1 & ":A" & lastrow), .Range("A" & i).Value) = 0 Then
        Me.Ritnummer.AddItem .Range("A" & i)
        Me.Produkt.AddItem .Range("F" & i)
     End If
    Next i
    Last edited by nimrod; 11-16-2014 at 03:05 PM.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,493

    Re: Remove duplicates out of combobox

    Open the sample workbook, click the show userform, I usually populate the combobox with the Userform_initialize code.
    Anyway it looks like this, you also need to clear the ComboBox each time you populate it or it will just keep getting bigger.

    Private Sub UserForm_Initialize()
    Dim c As Range, r As Range
    Dim rws As Long, y As Integer
    
        rws = ActiveSheet.UsedRange.Columns(1).Rows.Count
        
        ComboBox1.Clear
        Set r = Range(Cells(2, 1), Cells(rws, 1))
        For Each c In r.Cells
            y = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(c.Row, 1)), c)
            If y = 1 Then ComboBox1.AddItem c
        Next c
        
    ComboBox1.SetFocus
    Application.SendKeys "^{F4}"
    
    End Sub
    Private Sub CommandButton1_Click()
    Unload Me
    End Sub
    Attached Files Attached Files
    Last edited by davesexcel; 11-16-2014 at 05:14 PM.

  4. #4
    Registered User
    Join Date
    10-05-2014
    Location
    nl
    MS-Off Ver
    8
    Posts
    13

    Re: Remove duplicates out of combobox

    Thanks it Works

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove duplicates and sort data for populating a combobox using VBA
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2013, 05:28 AM
  2. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  3. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  4. Replies: 2
    Last Post: 03-20-2011, 11:19 AM
  5. Removing Duplicates from ComboBox and Populating one combobox based on another
    By kbmtech in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2010, 11:17 PM

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