+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Jimv
    Guest

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

    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

  2. #2
    Anne Troy
    Guest

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

    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/m...plications.htm

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

    "Jimv" <[email protected]> wrote in message
    news:[email protected]...
    >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




  3. #3
    Jimv
    Guest

    RE: I need help with deleting duplicate, and the original cell, ro

    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


  4. #4
    JMB
    Guest

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

    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


  5. #5
    JMB
    Guest

    RE: I need help with deleting duplicate, and the original cell, ro

    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


  6. #6
    Jimv
    Guest

    Re: I need help with deleting duplicate, and the original cell, ro

    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.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/m...plications.htm
    >
    > ************
    > Hope it helps!
    > Anne Troy
    > www.OfficeArticles.com
    >
    > "Jimv" <[email protected]> wrote in message
    > news:[email protected]...
    > >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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1