I have a list of email addresses, I need to purge out the "unsubscribes". I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim

Open your workbook. Hit Alt+F11 to open the VB Editor. From the menu, choose
Insert--Module. Paste the following code into the code window that appears
at right.
---code starts below---
Option Explicit

Sub deleteDups()
Dim rngCell As Range
Dim lngRow As Long

For Each rngCell In ActiveSheet.UsedRange.Columns(1).Cells
If Application.WorksheetFunction.CountIf(rngCell.Enti reColumn,
rngCell) 1 Then
rngCell.Cells(1, 2).Value = "TAGGED"
End If
Next

For lngRow = ActiveSheet.UsedRange.Columns(1).Rows.Count To 1 Step -1
Set rngCell = ActiveSheet.UsedRange.Columns(1).Cells(lngRow, 1)
If rngCell.Cells(1, 2).Value = "TAGGED" Then
rngCell.EntireRow.Delete
End If
Next
End Sub
---code ends above---

Hit the SAVE diskette if you want to save the macro for next time.
Close the VB Editor window.
Tools--Macro--Macros and double-click deleteDups. You can assign it to a
toolbar button or shortcut key, too. Here's how:
http://www.officearticles.com/misc/m...ica tions.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Jimv" wrote in message
...
I have a list of email addresses, I need to purge out the "unsubscribes".
I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim

Thank you!!! It works.

Jim

"JMB" wrote:

Select the column containing your data, then try running this macro. It will
search each item for "unsubscribe" (I am assuming the ones you want deleted
will contain this word) and delete the entire row(s).

If you want to change it to a more generic search (delete all instances of
every item that is duplicated) then change

If InStr(1, x.Value, "unsubscribe", 1) 0 Then

To:
If Application.CountIf(Selection, x.Value) 1 Then


Sub DeleteUnsubscribers()
Dim x As Range
Dim Dupes As Range

For Each x In Selection
If InStr(1, x.Value, "unsubscribe", 1) 0 Then
If Dupes Is Nothing Then
Set Dupes = x
Else: Set Dupes = Union(Dupes, x)
End If
End If
Next x

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

End Sub


"Jimv" wrote:

I have a list of email addresses, I need to purge out the "unsubscribes". I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim

Select the column containing your data, then try running this macro. It will
search each item for "unsubscribe" (I am assuming the ones you want deleted
will contain this word) and delete the entire row(s).

If you want to change it to a more generic search (delete all instances of
every item that is duplicated) then change

If InStr(1, x.Value, "unsubscribe", 1) 0 Then

To:
If Application.CountIf(Selection, x.Value) 1 Then


Sub DeleteUnsubscribers()
Dim x As Range
Dim Dupes As Range

For Each x In Selection
If InStr(1, x.Value, "unsubscribe", 1) 0 Then
If Dupes Is Nothing Then
Set Dupes = x
Else: Set Dupes = Union(Dupes, x)
End If
End If
Next x

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

End Sub


"Jimv" wrote:

I have a list of email addresses, I need to purge out the "unsubscribes". I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim

Glad to help.

"Jimv" wrote:

Thank you!!! It works.

Jim

"JMB" wrote:

Select the column containing your data, then try running this macro. It will
search each item for "unsubscribe" (I am assuming the ones you want deleted
will contain this word) and delete the entire row(s).

If you want to change it to a more generic search (delete all instances of
every item that is duplicated) then change

If InStr(1, x.Value, "unsubscribe", 1) 0 Then

To:
If Application.CountIf(Selection, x.Value) 1 Then


Sub DeleteUnsubscribers()
Dim x As Range
Dim Dupes As Range

For Each x In Selection
If InStr(1, x.Value, "unsubscribe", 1) 0 Then
If Dupes Is Nothing Then
Set Dupes = x
Else: Set Dupes = Union(Dupes, x)
End If
End If
Next x

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

End Sub


"Jimv" wrote:

I have a list of email addresses, I need to purge out the "unsubscribes". I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim

Anne,

Thank you this solution works the best!!

Jim

"Anne Troy" wrote:

Open your workbook. Hit Alt+F11 to open the VB Editor. From the menu, choose
Insert--Module. Paste the following code into the code window that appears
at right.
---code starts below---
Option Explicit

Sub deleteDups()
Dim rngCell As Range
Dim lngRow As Long

For Each rngCell In ActiveSheet.UsedRange.Columns(1).Cells
If Application.WorksheetFunction.CountIf(rngCell.Enti reColumn,
rngCell) 1 Then
rngCell.Cells(1, 2).Value = "TAGGED"
End If
Next

For lngRow = ActiveSheet.UsedRange.Columns(1).Rows.Count To 1 Step -1
Set rngCell = ActiveSheet.UsedRange.Columns(1).Cells(lngRow, 1)
If rngCell.Cells(1, 2).Value = "TAGGED" Then
rngCell.EntireRow.Delete
End If
Next
End Sub
---code ends above---

Hit the SAVE diskette if you want to save the macro for next time.
Close the VB Editor window.
Tools--Macro--Macros and double-click deleteDups. You can assign it to a
toolbar button or shortcut key, too. Here's how:
http://www.officearticles.com/misc/m...ica tions.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Jimv" wrote in message
...
I have a list of email addresses, I need to purge out the "unsubscribes".
I
can find all types of macros that will delete the duplicate entry, but
nothing that will also purge the original, or unique counterpart of the
duplicate.
Can anyone help with this.

thanks

Jim