Removing duplicates
Ok Dave, I shall do as you recommended.
Many thanks for your help.
--
Tdp
"Dave Peterson" wrote:
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.
Tdp wrote:
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!
--
Tdp
"Dave Peterson" wrote:
This is the only code that I saw that adds to sheet5:
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
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
Set LastRow = Sheet5.Range("a100").End(xlUp)
msgbox lastrow.address
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox4.Text
LastRow.Offset(1, 2).Value = TextBox5.Text
Just to see what lastrow was when the code ran.
Tdp wrote:
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.
--
Tdp
"Dave Peterson" wrote:
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?
Tdp wrote:
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 sheet5(customer2). It works well in deleting any
duplicate names and leaving the lastest entry. The trouble I am having is
that on sheet5(customer2) it adds numbers to the name column("A") as well as
deleting any duplicates.
Any ideas
--
Tdp
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|