Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete dups (Excel 2003)

    Hi,
    I have a spreadsheet of close to 20000 records (rows) from which I would like to delete the duplicates. Iíve attached the sample workbook. After I did a search on this forum, I have tried to apply some of the suggestion given and with out any success.
    Can you please give me some feedback, as to the best approach for this task? Creating an access db, Macro, or vba etc.


    Thanks,
    OCM
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete dups (Excel 2003)

    You could run the following macro:

    Sub DeleteDups()
    Dim r As Long
    Dim n As Long
    n = Range("Q" & Rows.Count).End(xlUp).Row
    For r = n To 2 Step -1
    If Range("Q" & r) = True Then
    Range("Q" & r).EntireRow.Delete
    End If
    Next r
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete dups (Excel 2003)

    Thanks Hans for the reply.
    1. Where can I find instruction to run macro
    2. It's possible someone wanted to look at the dups (before I delete them) Is there a way to somehow highlight it etc.
    3. This will be an on going task and in the future, I would like to use access and is there insructions (or a link) how to perform this task?

    Thank you in advance.
    OCM

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete dups (Excel 2003)

    1a) To create the macro:
    - Select the lines between Sub DeleteDups and End Sub in my previous reply.
    - Press Ctrl+C to copy them.
    - Switch to Excel.
    - Select Tools | Macro | Macros... (or press Alt+F8)
    - Type the name DeleteDups in the Macro Name box.
    - Click Create.
    - You'll see

    Sub DeleteDups()

    End Sub

    - Click in the blank line in between.
    - Press Ctrl+V to paste the code.
    - Switch back to Excel.

    1b) To run the macro:
    - Select Tools | Macro | Macros... or press Alt+F8.
    - Select DeleteDups in the list of available macros.
    - Click Run.

    Also see Legare Coleman's Personal.xls Tutorial (All).

    2) You've already got conditional formatting to highlight duplicate rows.

    3) Before you start in Access, I'd follow a training course or study a good book about Access.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete dups (Excel 2003)

    Hans,
    Thanks it works as planned. One question though, in your previous macro line n=Range("Q" & Rows.Count).End(xlUP).Row, I assume "Q" is the colmn where I have COUNTIF formula, correct?

    Thanks,
    OCM

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete dups (Excel 2003)

    Yes, indeed.

Posting Permissions

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