Removing duplicates

T

Tdp

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
 
D

Dave Peterson

The only thing that I see that adds data is that stuff at the top of the
routine. Is that what you mean?

I do think you have a bug in your code.

Instead of:
For iRow = FirstRow To LastRowNumber Step 1

I'd use:
For iRow = lastrownumber to FirstRow step -1

But that'll end up keeping the first name (lowest row number).

Is that ok?
 
T

Tdp

No still having the same problem.
In sheet5(customer2) Col("A"), the name col, it seems to replace the
duplicate name with the row number.
 
D

Dave Peterson

This is the only code that I saw that adds to sheet5:

If lastrow isn't really the last cell used (maybe column A is empty when column
B isn't), then that could cause the trouble.

I'd add

Just to see what lastrow was when the code ran.
No still having the same problem.
In sheet5(customer2) Col("A"), the name col, it seems to replace the
duplicate name with the row number.
 
T

Tdp

I'v done that and it tells me that it is selecting the last row.
You mention a bug in the code is there a way to search for that and repair?
Quite often the whole thing stops and I get a message the following message:
Excell has encounted a problem and needs to close down.
I'v been trying to find out what was doing that, that is why I guessed it
could be this problem, but now I'm not so confident!
 
D

Dave Peterson

I think the bug in your code is that you don't work from the bottom up. My
suggestion was to use that line with step -1.

You may want to build a small test workbook and see if your code works there. I
don't see anything that would cause the error--and it's more like excel doesn't
like that workbook. You may want to rebuild it.
I'v done that and it tells me that it is selecting the last row.
You mention a bug in the code is there a way to search for that and repair?
Quite often the whole thing stops and I get a message the following message:
Excell has encounted a problem and needs to close down.
I'v been trying to find out what was doing that, that is why I guessed it
could be this problem, but now I'm not so confident!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top