Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing duplicates
I have the following code:
Private Sub CommandButton1_Click() Dim LastRow As Range Dim iRow As Long Dim FirstRow As Long Dim LastRowNumber As Long Dim wks As Worksheet Application.DisplayAlerts = False Application.EnableEvents = False Set LastRow = Sheet2.Range("a100").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text LastRow.Offset(1, 2).Value = TextBox3.Text Set LastRow = Sheet5.Range("a100").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox4.Text LastRow.Offset(1, 2).Value = TextBox5.Text MsgBox ("Data has been entered") For Each wks In Worksheets(Array("customers", "customers2")) With wks FirstRow = 2 'headers in row 1 'Changed LastRow to LastRowNumber 'Dim LastRowNumber as Long added above 'LastRow is declared as Range 'We need it to be Long LastRowNumber = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRowNumber Step 1 If Application.CountIf(.Range("a2").EntireColumn, _ .Cells(iRow, "A").Value) 1 Then 'it's a duplicate MsgBox .Cells(iRow, "A").Value .Rows(iRow).Delete End If Next iRow End With Next wks Application.EnableEvents = True Application.DisplayAlerts = True End Sub TextBox1 is where the names are entered, the rest are dates. This is at the end of a code that saves the data to two sheets. Sheet29customer) and sheet3(customer2). It works well in deleting any duplicate names and leaving the lastest entry. The trouble I am having is that on sheet3(customer2) it adds numbers to the name column("A") as well as deleting any duplicates. Any ideas -- Tdp |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Duplicates | Excel Worksheet Functions | |||
Removing Duplicates from a list | Excel Worksheet Functions | |||
removing duplicates | Excel Discussion (Misc queries) | |||
Removing Duplicates | Excel Discussion (Misc queries) | |||
Removing Duplicates | Excel Worksheet Functions |