Remove non-consecutive duplicates from combobox 1

[zimGirl]

i know how to remove duplicates from consecutive list in a combobox.
rent
rent
rent
count
count

Dim varI As Integer
Dim strTemp As String
Do While varI <= cmbCategory.ListCount
If varI = cmbCategory.ListCount Then
Exit Sub
End If
If strTemp = cmbCategory.List(varI) Then
cmbCategory.RemoveItem (varI)
intK = intK + 1
Else
strTemp = cmbCategory.List(varI)
varI = varI + 1

End If
Loop

How do you remove duplicates that are not consecutive?
rent
count
rent
rent
count
count
the above code only removes the two consecutive values
leaving
rent
count
rent
count
I want it to leave
rent
count

 

[CajunCenturion]

How is the combo box being loaded initially?

 

[zimGirl]

loaded from a .csv file.
File is about 195000 lines long and one column
from the file is used to fill combo box

 

[CajunCenturion]

Couple of things that you might want to consder. First, move the .csv file into a table from which you can remove the duplicates, or you might consider establishing an ADO connection to the .csv files and doing a SELECT DISTINCT.

 

[zimGirl]

I only have excel and it will not open the whole file, only a third of the file can be opened. I can not remove the duplicates from the file as they relate to other data and will be used later in other calculations. I am currently using AutoCAD VBA for this purpose, therefore can not use an ADO connection.
What is a SELECT DISTINCT? and how does it work? Form what I read it is not going to help me in doing the.

 

[CajunCenturion]

Can you post the actual code that you use to populate the combobox?

 

[zimGirl]

The code opens the text file and then fills an array. At each point it places an item into the combobox

Private Sub cmdInput_Click()
On Error Resume Next
Dim blnError As Boolean
blnError = True
blnArrayFilled = False
varFileNum = FreeFile [COLOR=green] 'set as freefile so that program can set any file open as num[/color]
start_time = Timer
[COLOR=green]'Set a filter for opening files to open specific files only[/color]
dlgMain.Filter = "Text Files(*.CSV)|*.csv|"
'Data Files(*.DTA)|*.dta|Text Files (*.TXT)|*.txt"
dlgMain.ShowOpen             'show file open dialog box
If dlgMain.filename <> "" Then
    Open dlgMain.filename For Input As varFileNum
 If blnError <> False Then
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(dlgMain.filename)
    Set a = f.OpenAsTextStream(1)
   [COLOR=green] 'count the number of lines in the text file[/color]
    Do While a.AtEndOfStream <> True
    [COLOR=green]''place a line into a variable[/color]
        retstring = a.ReadLine
    [COLOR=green]''count that line[/color]
        intI = intI + 1
    Loop
   [COLOR=green]''count the number of commas[/color]
       [COLOR=green] ''PROBLEM: Skips consecutive commas therefore is two down on number of columns[/color]
    Call CountColumns
    [COLOR=green]'reset the text stream[/color]
    Set a = f.OpenAsTextStream(1)
   [COLOR=green] 'redefine the array[/color]
    ReDim TempArray(intI - 1, intColumnCount + 2)
    [COLOR=green]''loop through the text file one line at a time and place into the array[/color]
       I = 0
        Do While a.AtEndOfStream <> True
            retstring = a.ReadLine
            varText = Split(retstring, ",")
            For J = 0 To intColumnCount + 2
                varText = Split(retstring, ",")
                TempArray(I, J) = varText(J)
            Next J
           [COLOR=green]''fills the combo box here[/color]
            cmbPropType.AddItem TempArray(I, 9)
            I = I + 1
        Loop
   MsgBox ("Sucess")
Else
    Close #varFileNum
    Exit Sub
    End If
End If
dlgMain.filename = ""
blnArrayFilled = True
Close #varFileNum
 
    stop_time = Timer

    lblInput.Caption = Format$(stop_time - start_time, _
        "0.00") & " sec"
  End Sub

 

[CajunCenturion]

Inside the following loop, at the indicated location, you can check to see if the item has not already been loaded into the combobox.

Do While a.AtEndOfStream <> True
   retstring = a.ReadLine
   varText = Split(retstring, ",")
   For J = 0 To intColumnCount + 2
      varText = Split(retstring, ",")
      TempArray(I, J) = varText(J)
   Next J
   ''fills the combo box here
[COLOR=blue]
   IsLoaded = False
   For J = 0 To cmbPropType.ListCount - 1
      If (cmbPropType.Column(0, J) = TempArray(I, 9)) Then
         IsLoaded = True
         Exit For
      End If
      If (IsLoaded = False) Then      
         cmbPropType.AddItem TempArray(I, 9)
      Endif
[/color]
   I = I + 1
Loop

 

[zimGirl]

thanks for the post CajunCenturion. This works perfect. Just one addition though:

Do While a.AtEndOfStream <> True
   retstring = a.ReadLine
   varText = Split(retstring, ",")
   For J = 0 To intColumnCount + 2
      varText = Split(retstring, ",")
      TempArray(I, J) = varText(J)
   Next J
   ''fills the combo box here

   IsLoaded = False
   For J = 0 To cmbPropType.ListCount - 1
      If (cmbPropType.Column(0, J) = TempArray(I, 9)) Then
         IsLoaded = True
         Exit For
      End If
   [COLOR=blue]Next     ''Have to put a next here or it never gets into the loop[/color]
      If (IsLoaded = False) Then      
         cmbPropType.AddItem TempArray(I, 9)
      Endif

   I = I + 1
Loop

 

[CajunCenturion]

Good catch, that Next is rather important.