Hi Greg,

Why not use the Advanced Filter | Unique Records and use the filtered list
as your source?


---
Regards,
Norman



"browie" wrote in message
...
Hi all I have a userform which I have linked a combobox upto column "A" in
my worksheet.
What I have done is place some code that copies the information in this
box when exited into two postions in the workbook.

The problem I am having is when something new is added it is ok and it
just adds to the list, but when an older item is used it places it into
the list aswell so I then start having duplicates.
Is there a code I can use on the combobox to stop this from occuring?

If not how can I change this code to search the whole column and remove
the duplicates.


This is the code I use to put the information into the worksheets, It is
the section for sheet150 which is called "dayoptions" where I am having
the problem with duplicates.

Private Sub CommandButton1_Click()

R = 46
ActiveSheet.Cells(R, 2).Value = UCase(ComboBox1.Text)
R = 46
ActiveSheet.Cells(R, 3).Value = UCase(TextBox1.Text)
R = 46
ActiveSheet.Cells(R, 4).Value = UCase(TextBox4.Text)
R = 46
ActiveSheet.Cells(R, 5).Value = UCase(TextBox6.Text)

R = 46
ActiveSheet.Cells(R, 6).Value = UCase(ComboBox2.Text)
R = 46
ActiveSheet.Cells(R, 7).Value = UCase(TextBox2.Text)
Sheet150.Range("A1").Insert
R = 46
ActiveSheet.Cells(R, 8).Value = UCase(TextBox3.Text)
R = 46
ActiveSheet.Cells(R, 9).Value = UCase(TextBox5.Text)

R = 1
Sheet150.Cells(R, 1).Value = UCase(ComboBox1.Text)
Sheet150.Range("A1").Insert
R = 1
Sheet150.Cells(R, 1).Value = UCase(ComboBox2.Text)
With Worksheets("dayoptions")
.Range("A1:A65536").Sort Key1:=.Range("A1")
End With

Unload Me
DAYOPTIONSDAYS.Show

end sub

This is the code I use to remove the duplicate as you can see it is very
complicated, there must be an easier way.

Sub SHUTDOWN()
'
' SHUTDOWN Macro
' Macro recorded 19/08/2005 by Greg
'

'
Sheets("DAYOPTIONS").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select
ActiveCell.FormulaR1C1 = "=C[-1]+COUNTIF(C[-1],RC[-1])"
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Range("B1").Select
Selection.Copy
Range("B2:B500").Select
ActiveSheet.Paste
Range("C1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-1]1,""YES"","""")"
Range("C1").Select
Selection.Copy
Range("C2:C500").Select
ActiveSheet.Paste
Call TRY

End Sub
Sub TRY()
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 3
rw = 1
With Worksheets("DAYOPTIONS")
Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete
End If
Next
Call TRYER

End Sub
Sub TRYER()
Columns("B:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 2
rw = 1
With Worksheets("DAYOPTIONS")
Set rng = .Range(.Cells(1, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "0" Then
cell.EntireRow.Delete
End If
Next

Columns("B:C").Select
Range("B247").Activate
Selection.ClearContents
Range("A247").Select
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 1
Range("A1").Select
End Sub

Sorry for the long question.
Thanks in advance

Greg