I need help with deleting duplicate, and the original cell, row.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.EntireColumn,
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/methods_to_run_a_macro_in_microsoft_office_applications.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com
 
Thank you!!! It works.

Jim

JMB said:
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
Click to expand...
 
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
 
Back
Top