Duplicates in column

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



0
laptopgb (91)
8/21/2005 1:59:56 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
549 Views

Similar Articles

[PageSpeed] 13

Hi Greg,

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


---
Regards,
Norman



"browie" <laptopgb@ihug.com.au> wrote in message 
news:de8n6l$r12$1@lust.ihug.co.nz...
> 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
>
>
> 


0
normanjones (1047)
8/21/2005 2:10:58 AM
Thanks Norman
will give it a go just did not think of that at all.

Greg

"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:O9jYTWfpFHA.3380@TK2MSFTNGP12.phx.gbl...
> Hi Greg,
>
> Why not  use the Advanced Filter | Unique Records and use the filtered 
> list as your source?
>
>
> ---
> Regards,
> Norman
>
>
>
> "browie" <laptopgb@ihug.com.au> wrote in message 
> news:de8n6l$r12$1@lust.ihug.co.nz...
>> 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
>>
>>
>>
>
> 


0
laptopgb (91)
8/21/2005 2:18:43 AM
I would suggest that before you add the item to the list you check if it is
there already, that way you list should remain duplicate free without having
to clear up afterwards.  If you need to sort the lsit do that at the same
time.  So the pseudo code would be....

combox value is entered then
check is value in list
if not add it to the end, sort the list, and refresh the combo box, set
index to value entered

If you wish to pursue this let me know and I'll provide some code.


-- 
Cheers
Nigel



"browie" <laptopgb@ihug.com.au> wrote in message
news:de8n6l$r12$1@lust.ihug.co.nz...
> 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
>
>
>


0
nigel-sw (9)
8/21/2005 5:42:43 AM
Thanks Nigel
I would like some help with that just not sure how to?

Greg
"Nigel" <nigel-sw@suxnospampanet.com> wrote in message 
news:eexXoMhpFHA.2976@TK2MSFTNGP12.phx.gbl...
>I would suggest that before you add the item to the list you check if it is
> there already, that way you list should remain duplicate free without 
> having
> to clear up afterwards.  If you need to sort the lsit do that at the same
> time.  So the pseudo code would be....
>
> combox value is entered then
> check is value in list
> if not add it to the end, sort the list, and refresh the combo box, set
> index to value entered
>
> If you wish to pursue this let me know and I'll provide some code.
>
>
> -- 
> Cheers
> Nigel
>
>
>
> "browie" <laptopgb@ihug.com.au> wrote in message
> news:de8n6l$r12$1@lust.ihug.co.nz...
>> 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
>>
>>
>>
>
> 


0
laptopgb (91)
8/21/2005 10:18:08 AM
Not sure where your "value" is coming from, or what column/cell it is going 
to.  But in general

If Worksheetfunction.Countif(Sheets("MySheet"),Range("A:A"),"value") = 0 
then
    ' code to add item
Else msgbox "Item already exists"
    ' what do you want to do
End If

-- 
steveB

Remove "AYN" from email to respond
"browie" <laptopgb@ihug.com.au> wrote in message 
news:de9kco$lkl$1@lust.ihug.co.nz...
> Thanks Nigel
> I would like some help with that just not sure how to?
>
> Greg
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message 
> news:eexXoMhpFHA.2976@TK2MSFTNGP12.phx.gbl...
>>I would suggest that before you add the item to the list you check if it 
>>is
>> there already, that way you list should remain duplicate free without 
>> having
>> to clear up afterwards.  If you need to sort the lsit do that at the same
>> time.  So the pseudo code would be....
>>
>> combox value is entered then
>> check is value in list
>> if not add it to the end, sort the list, and refresh the combo box, set
>> index to value entered
>>
>> If you wish to pursue this let me know and I'll provide some code.
>>
>>
>> -- 
>> Cheers
>> Nigel
>>
>>
>>
>> "browie" <laptopgb@ihug.com.au> wrote in message
>> news:de8n6l$r12$1@lust.ihug.co.nz...
>>> 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
>>>
>>>
>>>
>>
>>
>
> 


0
8/21/2005 10:50:35 PM
Reply:

Similar Artilces:

Vlookup with variable column reference
Help! I am trying to link two workbooks with a vlookup that searches for the last populated columns' entry in a range (see example below). 27/03 28/03 29/03 30/03 31/03 Data 6 4 Is there a way of doing this in a function? In a macro I would use the Range().End(xltoleft) code but I cannot figure out how to do something similar in a worksheet function. I basically want the lookup column to start at day 31 and make its way backwards until it finds a value. Another issue is that the seemingly blank cells contain formula - ie. not empty. Any ideas? Thanks!! Jen 1 ...

variable column names in a report
i have an application where the user can enter self selected column names which then in turn are added to a table... application works fine. however, when it comes to reporting, how do i go about adding those self entered columns in a report? any help would be appreciated Do you actually need this in a report or can the results be pushed to Excel for printing? -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "k2sarah" <k2sarah@discussions.microsoft.com> wrote in message news:C56B6FD1-2F5B-45D5-8760-A61E3CA41495@microsoft.c...

Hide Cell Content if no data in previous columns
I've made this work many years ago but now can't remember how. I have a job invoice to where the rate is 40.00/hour. This 40.00 is used in calculation to determine the total amount of pay owed. (20 hours worked * 40.00/hour). The boss requires that the hourly rate be shown for each job on the invoice, so that means it is in a column to where the amount is repeated all the way down. All I want is for the contents of that particular column NOT to show (show as blank), if no job has been entered. In other words, if there is no data entered on that particular row, hide the cont...

Copy one Column Across One Row
Hey guys, I would like to take one column of data and copy it across one row., so, I want the data to go left to right across the spreadsheet instead of top to bottom. If you have a suggestion to solve this, macro or whatever, could you please email it to me at: krea@dslextreme.com thanks, kevin rea Kevin If the column contains no more than 255 rows of data(256 if you are pasting below row 256) you can copy it and select B1 and Paste Special>Transpose>OK>Esc. Gord Dibben Excel MVP On Wed, 14 Apr 2004 17:23:46 -0700, <krea@dslextreme.com> wrote: >Hey guys, > &g...

check number duplication
How does one get Money 2006 to flag duplication of check numbers and why doesn't the program flag such occurences? This is only an occassional problem but I have never found a way to get Money to warn me or to flag such an occurence. The only place I know of where it does that is in the Monthly Report. Which is a bit late! ;-) -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "bergrrt" <bergrrt@discussions.microsof...

Totals in Stacked Column?
I can't display the totals in a stacked column chart. I'm trying to show the total amounts for each column but the data labels only apply to each series. Never mind - Andy Pope's website had the answer. "EllenM" wrote: > I can't display the totals in a stacked column chart. I'm trying to show the > total amounts for each column but the data labels only apply to each series. ...

Trying to make a duplicate roster with a button.
Previously posted in wrong forum. 'HERE' (http://www.excelforum.com/showthread.php?t=265167) Thanks for the help guy -- virte ----------------------------------------------------------------------- virtex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1488 View this thread: http://www.excelforum.com/showthread.php?threadid=26517 ...

Creating Labels, Missing Column
I'm trying to create address labels. In my file I have the columns for "Street Address", "City", "State", "Zip Code" and "Country". The street address does not show up in the window to select what info I want on the label. Anyone know how to fix this??? Hi In your table you "may" have IDfield, 1stName, 2ndName, Street Address, City, State, Zip Code and Country. Create a query based on the table. Bring all the fields in the query and create a calculted culumn with something like Capital: Left([1stName],1) Create a new re...

How to update a portion of rows and columns between two files
I have a Master file with 20 columns of data for each of approximately 700 rows. Each month, I receive an update file that I need to use to update 10 columns of data for 3/4 of the rows in my Master file. There is a unique identifier in each file. Not all rows in the update file will be copied into the Master file and not all rows in the Master file will have updates. What is the best way to do this? I am familiar with VLookup. Shall I write aVLookup for each of the 10 columns in each row that need to be replaced so that I can grab the cell from the new file? Or, is there an easier way? ...

Clustered column graph with mixed data in 2nd column
I'm trying to create a clustered column graph with mixed data in the 2nd column and I cannot find a way to do it. Any suggestions? What's "mixed data"? Do you mean you want to build a stacked column for the second column in the cluster? If so, look here: http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "drhatt" <drhatt@discussions.microsoft.com> wrote in message news:648BC5CE-4DE9-433C-A824-325B6995D...

Columns on wrong side
When I open a book in excel, why is column A in Excel on the right side of the sheets, meaning it looks like this: P-O-N-M-L-K-J-I-H-G-F-E-D-C-B-A How do I change it to look the normal way from left to right when I open a new book in excel? Hi Seems you have set Excel to read from Right to Left - as is done in Arabic language. To set it right Go to menu Tools->Options Choose the International Tab In Right-to-Left Default direction choose Left-to-Right The order of column will be A-B ... Regards Sukhjeet "Drew" wrote: > When I open a book in excel, why is column A in Exce...

Duplicate Detection Problems
CRM 4 Prof We recently upgraded to V4 and put some duplicate detection rules in place. We have at least one record where: 1. We attempt to edit the record. 2. When we click Save, we are told a potential duplicate is detected. (This duplicate is an inactive record that we choose to ignore.) 3. We click Save Record. 4. The screen refreshes, and none of the edits are saved. This happens in both the Outlook client and the Web client. Assistance is appreciated. Talina UPDATE: I unpublished the Duplicate Detection Rule that governed contacts and the problem went away. Obviously, t...

Checkbox column #2
Hi, How do I create a checkbox column to contain boolean values ? Regards Bo "Bo Rasmussen" <krogenlund@hotmail.com> wrote in news:OtsOMgl6DHA.3648 @TK2MSFTNGP11.phx.gbl: > How do I create a checkbox column to contain boolean values ? > Excel doesn't support this directly. To get a checkbox you need to use VBA Forms or OLE controls, create a bunch of checkboxes, then link them to the cells. Check out View/Toolbars/Forms and View/Toolbars/Control Toolbox. Could you describe in more detail what you're looking for? -- My email address has an extra @ (s...

Wrapping text in column
In Excell 2007, how do you set a column to wrap text? I tried it and even though it seems to allow me to do it when I select column and check wrap text from Format Cells, it doesn't work. It only works when I select one cell and select wrap text. This is a bit irritating as I have many cells in a column to do this to. Thanks, Tom I find that this happens when I paste from Word. If I have the fields set as Vertical/Top and Wrap Text - then I paste any data from Word, it goes to Vertical/Bottom and NOT wrap text. If I paste data from my Textpad program that spans multiple line...

Text Box
I would like to have the same size text box on multiple pages that are linked. I have tried right clicking the text box, selecting Format Text Box and checking Apply Settings to New Text Boxes, but it doesn't appear to work. When I add text to the box and it runs out of space, Publisher asks if I want to insert a new text box and connect it, and I accept, the new text box is the wrong size, and sometimes comes in with a black border rather than the no line border I have designated. Is there another method to easily accomplish what I want? I have read several posts that recommend...

How do I make multi column stacked graphs in Excel
-- Ed Hi, See the links on Jon's page for Cluster Stacked charts. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Ed B wrote: ...

Subtotaling on column with IF statement results
I created a spreadsheet with one column having an IF statement - it calculates how much we should re-order per store based on what was sold. I asked it to subtotal for each store - the formula appears in the cell, but it does not calculate the subtotal. I tried saving the column as the values, but it still does not subtotal. When I typed in the numbers, it DID subtotal. What do I need to do so it will subtotal? Thanks for your help. -- STK By any chance are you importing the data from another application? Check out your numbers: does the cell contain an apostrophe and then the numb...

duplication validation
hi, i wanted to perform validation for duplication of entries in a column. i went to chip pearson's website http://www.cpearson.com/excel/NoDupEntry.htm and looked this up: =COUNTIF($A$1:$A$50,A1)=1. however, i am not getting the desired results. it validates for only the immediately previous entry. for example, if i enter duplicates in consecutive cells, it gives me a error. if the cells are far apart in the column, i do not get a error. what gives? thanks in advance, mac -- Message posted from http://www.ExcelForum.com The data validation formula works for me just fine. ...

Duplicate named ranges
Is it possible to have duplicate named ranges in a workbook? I am using Excel 2000 on a Windows 2000 system. I can acheive creating a duplicate named range in a workbook by doing a copy sheet into the same workbook but I am unable to create multiple named ranges in the ame workbook by other means. When I try to define the named ranges I get directed to the last copy defined. Is there a way around this? TIA George George, You can have workbook names that apply to the whole workbook, and worksheet names that only apply to that sheet and can be replicated on each sheet. To create the lat...

Duplicate Messages - Outlook
I have three POP3 accounts hooked up with Outlook 2002 running on windows XP. One of the accounts continues to give me multiples of the same message eveytime it conects to and downloads from the one server. This only happens with one account. Please advise how to fix this problem. Thanks in advance. Jim Blyth You have a corrupt (or duplicate message id) message sitting in your box that Outlook can't handle. When this happens with POP3, use Magic Mail Monitor to clear the difficult item. http://www.geeba.org/magic/ "J Blyth" <jblyth@pathcom.ca> wrote in message ...

KBQ232391
I recently had a problem with OWA on my Exchange 5.5 box stemming from an Outlook 2003 related issue. As part of the fix, I removed and added OWA back, ran Exchange 5.5 SP4 and a number of post SP4 fixes including several directly related to the Outlook 2k3/Exchange 5.5 problem. However, I'm now receiving the following error: "The delivery of a message failed due to error 000004DD." A quick check of finds that this is a known problem first corrected in SP3. My problem is I'm already running SP4 with post SP4 fixes. How can I get rid of this problem? ...

Lines Between Columns
Publisher 2003 I have a newsletter page that is formatted to be double column. I want to place a 0.5 point line between the columns. Right clicking within the double columns then clicking on format text box>colors and lines then clicking the box in preview that is for the center line only nothing happens to the menu on the left. The "dashed", "style" and "weight " boxes are grayed out. Out of the six boxes shown there, the only two that activate the above quoted boxes are the two on the right of the bottom row (frame and frame with line down the middle). I ...

stop returning column headings from DB query
I put together a query to go against Oracle that returns a block of data to the spreadsheet. Is there an option somewhere to have the "Return Data to Excel" do so without also returning the column headings? I want the data only. thanks - Russ There are 2 places that you can change that setting..... 1)When you return data from MS Query the dialog where you select the location for the data has a Properties button. Click that button UNcheck: Include Field Names.......click [OK] Continue selecting the query destination OR 2)After the data has been pulled from Oracle Right-click...

Incrementing/decrementing column characters using only worksheet functions?
How can I increment and decrement column characters/letters using worksheet functions? I have a list of 5 characters corresponding to columns. The first character might be A, for column 1. How can I get the second character in the list to automatically configure itself to be B, the third C, the fourth D and the fifth E? That is, I want to set this up so that the second character is linked to the first, the third to the second and so on. That way if I change the first character from A to D then the second character in the list will automatically become E, the third will change to F, the f...

Locking rows (not columns)?
Hi I download and store my bank statements in an Excel spreadsheet. This is maintained in conventional 'bank statement' format; ie columns for date, payee, amount, account balance, with one statement entry per row in chronological order. I find it very useful to be able manipulate the spreadsheet eg by sorting alphabetically by payee, so I can easily see all payments made to a particular company grouped together. However, it's very easy to use the 'sort' function incorrectly, so that the date/payee/amount columns become completely dissociated from each other, and if some...