Filter out duplicate data
 

I'm trying to write a macro that will filter out duplicate entries in a
column so that I can use the entries in that column to populate a combobox.
Here's an example of what I'm trying to do.

A
A
B
B
B
B
C
B
A
C
C
D
D

I want to filter the above data using code so that the end result is:

A
B
C
D

This doesn't seem like it should be that diffucult, but I'm having problems.
Maybe I just need a nap.

Anyways, any help provided will be much appreciated.

Thanks,
Jason

Filter out duplicate data
 

here's something i cobbled together with the help of others here. see if you can
adapt it.

it uses a hard coded range A1:A!0, but you can code it for a variable number of
rows. it adds each letter to a collection. the collection creates an error when
a dupe is added, so there is an on error resume next statement. some don't like
to rely on an error, but it has always worked for me.

then it sorts the letters and adds them to combobox1 on userform1 when it is
activated

Private Sub UserForm_Activate()
Dim NoDupes As Collection
Dim rng As Range
Dim cell As Range
Dim i As Long
Dim j As Long
Dim Swap1, Swap2, Item

Set NoDupes = New Collection
Set rng = Range("A1:A10")

On Error Resume Next
For Each cell In rng
NoDupes.Add cell.Value, CStr(cell.Value)
Next cell
' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
UserForm1.ComboBox1.AddItem Item
Next Item
End Sub

--


Gary


"JayWes" wrote in message
...
I'm trying to write a macro that will filter out duplicate entries in a
column so that I can use the entries in that column to populate a combobox.
Here's an example of what I'm trying to do.

A
A
B
B
B
B
C
B
A
C
C
D
D

I want to filter the above data using code so that the end result is:

A
B
C
D

This doesn't seem like it should be that diffucult, but I'm having problems.
Maybe I just need a nap.

Anyways, any help provided will be much appreciated.

Thanks,
Jason

Filter out duplicate data
 

If you use Advanced filter you can create a column of unique records. Record
a macro while doing this for the VB syntax.
--
Regards,

OssieMac


"JayWes" wrote:

I'm trying to write a macro that will filter out duplicate entries in a
column so that I can use the entries in that column to populate a combobox.
Here's an example of what I'm trying to do.

A
A
B
B
B
B
C
B
A
C
C
D
D

I want to filter the above data using code so that the end result is:

A
B
C
D

This doesn't seem like it should be that diffucult, but I'm having problems.
Maybe I just need a nap.

Anyways, any help provided will be much appreciated.

Thanks,
Jason

Filter out duplicate data
 

One more option. Needs xl2000 or later:

Private Sub UserForm_Initialize()
ComboBox1.List = NoDupes(Application.Transpose(Range("A1:A10").Valu e))
End Sub

Function NoDupes(arr As Variant) As Variant
Dim i As Integer, txt As String
txt = ";"
For i = LBound(arr) To UBound(arr)
If InStr(txt, ";" & arr(i) & ";") = 0 Then
txt = txt & arr(i) & ";"
End If
Next
txt = Mid$(txt, 2, Len(txt) - 2)
NoDupes = Split(txt, ";")
End Function