| 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing duplicates
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 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing duplicates
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing duplicates
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing duplicates
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing duplicates
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| 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 | |||
Linear Mode
