Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Delete duplicates stopped working (mso 2003 xppro)

    Hi

    Sometime ago I sourced some code for removing duplicate entries from an Excel spreadsheet. This worked fine for a while but suddenly it has decided that it doesn't want to work no more!

    The code is stored under my (user) Personal.xls (see attached) and appears to activate because it has warnings that pop up when opening new spreadsheets (starting Excel). I was using the code on data copied from our xml database. As I have stated it worked fine but suddenly NO! If I create some data to test it works fine.

    Can anyone help?
    Attached Files Attached Files
    cheers

    Phil Carter

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

    Re: Delete duplicates stopped working (mso 2003 xppro)

    You shouldn't store general macros in the ThisWorkbook module. That module is intended for workbook events. General macros should be in a standard module, the kind you create by selecting Insert | Module in the Visual Basic Editor.

    Apart from that, what exactly is the problem - doesn't the macro run at all, or does it present an error message? If the latter, what does the message say?

    If the code runs on other data (and it works OK for me too), there may be a problem with your XML data.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Delete duplicates stopped working (mso 2003 xppro)

    Hans thanks for getting back.

    The macro appears to work, in that it returns a message that no duplicate rows were deleted.

    I have attached a striped out version of the file i which I wish to dedup column E. This also has the macro copied into it. If it run from the document it works but that is not how it was designed to work.
    Attached Files Attached Files
    cheers

    Phil Carter

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

    Re: Delete duplicates stopped working (mso 2003 xppro)

    As I suggested, the problem is that you have put the macro in ThisWorkbook. If you put it in a standard module in your Personal.xls, it should work as intended.

    (When it is in ThisWorkbook, the macro assumes that ActiveSheet refers to ThisWorkbook, i.e. your Personal.xls)

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Delete duplicates stopped working (mso 2003 xppro)

    Hans

    Did as you suggested and all works fine.

    Many thanks once again
    cheers

    Phil Carter

Posting Permissions

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