Re: Deleting duplicate names

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I don't think you'd want to add anything to this code--it looks like it's the
code that populates the userform.

Do you have a button that exits the userform?

I used commandbutton99:

Option Explicit
Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton99_Click
End If
End Sub


The second procedure catches the X button in the top right corner of the
userform.

law wrote:

Dave,
Sorry about this, I had a look at that web site and it has gone over my
head. How do I insert the "delete duplicate row" code into the code below.

Private Sub CommandButton2_Click()
Dim FoundCell As Range
If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If
With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 16).Value
Me.TextBox17.Value = FoundCell.Offset(0, 18).Value
Me.TextBox18.Value = FoundCell.Offset(0, 15).Value
Me.TextBox19.Value = FoundCell.Offset(0, 19).Value
Me.TextBox20.Value = FoundCell.Offset(0, 17).Value
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(0, 20).Value) = "yes")
Me.CheckBox2.Value = CBool(LCase(FoundCell.Offset(0, 21).Value) = "yes")
Me.CheckBox3.Value = CBool(LCase(FoundCell.Offset(0, 22).Value) = "yes")
Me.CheckBox4.Value = CBool(LCase(FoundCell.Offset(0, 23).Value) = "yes")
Me.CheckBox5.Value = CBool(LCase(FoundCell.Offset(0, 24).Value) = "yes")
Me.CheckBox6.Value = CBool(LCase(FoundCell.Offset(0, 25).Value) = "yes")
Me.CheckBox7.Value = CBool(LCase(FoundCell.Offset(0, 26).Value) = "yes")
Me.CheckBox8.Value = CBool(LCase(FoundCell.Offset(0, 27).Value) = "yes")
Me.CheckBox9.Value = CBool(LCase(FoundCell.Offset(0, 28).Value) = "yes")
Me.CheckBox10.Value = CBool(LCase(FoundCell.Offset(0, 29).Value) = "yes")
Me.CheckBox11.Value = CBool(LCase(FoundCell.Offset(0, 30).Value) = "yes")
Me.CheckBox12.Value = CBool(LCase(FoundCell.Offset(0, 31).Value) = "yes")
Me.CheckBox13.Value = CBool(LCase(FoundCell.Offset(0, 33).Value) = "i
agree")
Me.CheckBox14.Value = CBool(LCase(FoundCell.Offset(0, 34).Value) = "i
disagree")
Me.ComboBox2.Value = FoundCell.Offset(0, 32).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value
End If
End Sub
--
law

"Dave Peterson" wrote:

Chip Pearson has lots of techniques to work with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

law wrote:

Im looking for a code that when close a userform(2), it looks for duplicate
names(col A sheet6) and deletes the whole row. The search must start at row 1.

--
law

--

Dave Peterson


--

Dave Peterson
.

Relevant Pages

  • Re: Questions regarding Userform/Macro
    ... Open the VB Editor then the Userform. ... Create a form with a StartNum Textbox, a NumCopy Textbox, a Print ... Private Sub CommandButton1_Click ... Dim NumCopies As String ...
    (microsoft.public.word.vba.general)
  • Re: Irregular Shape
    ... If you are asking how to have a "title" on a form that you removed the title bar from, then I would just place a Label on the UserForm. ... Dim MyRegionAs POINTAPI ... > Private Sub UserForm_Initialize ... > DeleteObject DefinedRegion ...
    (microsoft.public.excel.programming)
  • Re: Allow text in locked cells on a worksheet from a userform
    ... I would just save the filename that the user specified in the getopenfilename (in a module level variable or in a hidden label on the userform), then insert the picture using that filename. ... Dim fName As Variant 'could be boolean ... Private Sub CommandButton2_Click ...
    (microsoft.public.excel.programming)
  • Re: running program from userform
    ... changed etc. the private sub will act on these events ... If you wish to act on the control on the userform, ... Dim wbExtr As Workbook ...
    (microsoft.public.excel.programming)
  • Re: setting defaults for multiselect listboxes
    ... Keeping track of the items on a hidden worksheet sounds ideal. ... I tried moving the userform intialise code to workbook open ... Private Sub CommandButton1_Click ... Dim HWks As Worksheet ...
    (microsoft.public.excel.programming)