Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Problem solved

  1. #1
    New Member
    Join Date
    May 2002
    Location
    rachael beach
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dear All

    This is my first post! Hope someone can help.

    I would like to know how to automatically delete rows if a particular cell in that row is a duplicate.

    My 'A' column has a number of duplicates in it. If the cells in this column are duplicates I need to delete the whole row corresponding to that cell. Only this particular cell is a duplicate in the row - the other cells may not be duplicates.

    Hope this makes sense - I would be really greatful for any help

    Rachael



    [ This Message was edited by: rachaelb on 2002-05-08 08:46 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Insert this code into a standard module.
    1. Hit Alt-F11
    2. In VBA editor hit Alt-I (Insert Menu)
    3. Hit M (Insert Module)
    4. Paste this code as is
    5. Close VBA editor

    If you do not know how to run a macro,
    do the following from your spreadsheet
    which has the duplicates:

    Hi Alt-F8 and 2X-Click on "DeleteRows"
    to run the procedure.

    If you have any problems, repost.

    Tom



    Sub DeleteRows()
    Dim ColA_uRng, Cntr
    For Cntr = 1 To ActiveSheet.UsedRange.Columns(1).Rows.Count
    Set ColA_uRng = Range("A" & Cntr & ":A" & ActiveSheet. _
    UsedRange.Columns(1).Rows.Count)
    If Application.WorksheetFunction.CountIf(ColA_uRng, _
    Range("A" & Cntr).Value) > 1 Then
    Rows(Cntr).Delete
    Cntr = Cntr - 1
    End If
    Next
    End Sub




  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved".

    Excellent idea.....good thinking Tom

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you would prefer a way to simply mark the duplicate cells so that you can delete them manually:
    Assuming the Duplicates to test are in col A

    Enter this in cell A1 and the drag (fill) down as far as you have data:

    =countif($a$1:$a1,a1)

    This will display "1" for the first occurence, "2" for the second, etc.

    Manually delete any row with a number greater than 1.

    You can the autofilter the list for all numbers >1 and delete them manually

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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