I need help with a macro that will look at a smaller list of customer
names and VIN numbers and delete all of the duplicates off of another
master list with all customers on it. I would like to be able to select
the column that i want to sort--for example delete by VIN or name or
address..etc. Microsoft has one that is similar but is not easy to use
with over 10,000 names. Below is the code they provided. I have two
sheets and i am trying to compile them into one sheet with no duplicate
VINS and names. Any ideas would be great!
Thanks.


The following sample macro compares one (master) list against another
list, and deletes duplicate items in the second list that are also in
the master list. The first list is on Sheet1 in the range A1:A10. The
second list is on Sheet2 in the range A1:A100. To use the macro, select
either sheet, and then run the macro. Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub


--
zaq121
------------------------------------------------------------------------
zaq121's Profile: http://www.excelforum.com/member.php...o&userid=33919
View this thread: http://www.excelforum.com/showthread...hreadid=560416

Look at some of the methods he

http://www.cpearson.com/excel/duplicat.htm

--
Regards,
Tom Ogilvy



"zaq121" wrote:


I need help with a macro that will look at a smaller list of customer
names and VIN numbers and delete all of the duplicates off of another
master list with all customers on it. I would like to be able to select
the column that i want to sort--for example delete by VIN or name or
address..etc. Microsoft has one that is similar but is not easy to use
with over 10,000 names. Below is the code they provided. I have two
sheets and i am trying to compile them into one sheet with no duplicate
VINS and names. Any ideas would be great!
Thanks.


The following sample macro compares one (master) list against another
list, and deletes duplicate items in the second list that are also in
the master list. The first list is on Sheet1 in the range A1:A10. The
second list is on Sheet2 in the range A1:A100. To use the macro, select
either sheet, and then run the macro. Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub


--
zaq121
------------------------------------------------------------------------
zaq121's Profile: http://www.excelforum.com/member.php...o&userid=33919
View this thread: http://www.excelforum.com/showthread...hreadid=560416