Compare 2 lists macro help!

[zaq121]

Hello everyone,

I am new to macros in excel. I found a macro that microsoft posted about comparing two lists or sheets and deletes off of sheet 2, the ones that are on both sheets. It's hard to explain. For example, I have a large customer database of 4,000 + names on my sheet2. I want what is on my sheet 1 (which is my do not call list) to be taken off of sheet2 (the large list). The macro works, but the way it is set up makes me specify the range and column manually in the code. I am trying to modify the macro so i can select in the spreadsheet what i want it to sort through on both sheets. This way i can select the column i want to use in both sheets instead of manually changing it in the file. Below is the macro code. Does anyone have any ideas? Thank you.
--------------------------------------------------------------------------
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

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:A100")
' 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).EntireRow.Delete
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

[JMB]

First, I'll mention you have to be careful when looping through a list and
deleting rows. When you delete a row the remaining rows shift up causing the
macro to skip over some rows. Most folks loop through a list from the bottom
up or use a range variable inside the loop to track which rows should be
deleted, then delete all of the rows at once at the end.

One way is to set up two defined names. I am assuming the do not call list
is on Sheet1 in column A and your Data is on Sheet2 in column A. Change as
necessary. Click Insert/Names/Define and add

DoNotCall = Sheet1!$A:$A
Data = Sheet2!$A:$A

The macro would look like


Sub DelDups_TwoLists()
Dim rngDoNotCall As Range
Dim rngData As Range
Dim rngCell As Range
Dim rngDelete As Range

Application.ScreenUpdating = False
On Error GoTo ErrorHandler

With ThisWorkbook.Names("DoNotCall").RefersToRange
Set rngDoNotCall = Range(.Cells(1, 1), _
.Cells(.Rows.Count).End(xlUp))
End With
With ThisWorkbook.Names("Data").RefersToRange
Set rngData = Range(.Cells(1, 1), _
.Cells(.Rows.Count).End(xlUp))
End With

For Each rngCell In rngData
If IsNumeric(Application.Match(rngCell.Value, _
rngDoNotCall, 0)) Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

Application.ScreenUpdating = True
MsgBox "Done!"
Exit Sub

ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
Err.Clear
Exit Sub

End Sub



Hope it helps


"zaq121" wrote:

>
> Hello everyone,
>
> I am new to macros in excel. I found a macro that microsoft posted
> about comparing two lists or sheets and deletes off of sheet 2, the
> ones that are on both sheets. It's hard to explain. For example, I have
> a large customer database of 4,000 + names on my sheet2. I want what is
> on my sheet 1 (which is my do not call list) to be taken off of sheet2
> (the large list). The macro works, but the way it is set up makes me
> specify the range and column manually in the code. I am trying to
> modify the macro so i can select in the spreadsheet what i want it to
> sort through on both sheets. This way i can select the column i want to
> use in both sheets instead of manually changing it in the file. Below is
> the macro code. Does anyone have any ideas? Thank you.
> --------------------------------------------------------------------------
> Sub DelDups_TwoLists()
> Dim iListCount As Integer
> Dim iCtr As Integer
>
> 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:A100")
> ' 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).EntireRow.Delete
> ' 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=536978
>
>