Code for finding duplicates to ignore empty Cells

  • Hello all,


    In this Thread Jindon has provided a sample Workbook, with some fantastic Code.


    http://www.ozgrid.com/forum/showthread.php?t=162443


    And here is the Code:



    How would one tweak this Code to ignore empty cells, so that it does not consider empty cells as duplicates?


    Thank you.

  • Re: Code for finding duplicates to ignore empty Cells


    Just a thought, and not tested at all.


    Try changing:

    Code
    If Not .exists(r.Value) Or Then


    To:

    Code
    If Not .exists(r.Value) Or r.Value = vbNullString Then

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Code for finding duplicates to ignore empty Cells


    Much simpler


    Code
    Sub M_snb()
      with createobject("scripting.dictionary")
         for each it in sheets(1).cells(1).currentregion.specialcells(2)
            x0=.item(it)
         next
    
    
         msgbox join(.keys,vblf)
      end with
    End Sub
  • Re: Code for finding duplicates to ignore empty Cells


    @ KjBox,


    Thank you for your suggestion. In the attached sample Workbook, you will see that it works 100%, with the "Delete Duplicates" Button, on the first click only.:(


    If you click it again, it seems to regard empty Cells in the range A to C as "duplicates", and deletes data that should remain.


    @ snb,


    In the same attached sample Workbook, I have added your proposal, and a Button called "SNB". Because I have "Option Explicit" at the top of my Code, I had to add some Dim statements to your Code. It returns a vertical message box, too large for the screen. It does not delete duplicates. I am afraid I do not fully understand what your suggestion is supposed to do.:(


    Thank you guys!:)

  • Re: Code for finding duplicates to ignore empty Cells


    To remove all duplicates in A1:A100


    Code
    Sub M_snb()
        [A1:A100] = [if(A1:A100="","",if(countif(offset(A1,,,row(a1:A100)),A1:A100)=1,A1:A100,""))]
    End Sub
  • Re: Code for finding duplicates to ignore empty Cells


    As snb has done to specifically modify Jindons code you would use specialcells(2) as the current region - exclude blank cells (for more information see http://msdn.microsoft.com/en-us/libr.../ff196157.aspx)


    However I do not understand why you ask

    Quote

    How would one tweak this Code to ignore empty cells, so that it does not consider empty cells as duplicates?

    as Jindons code removes duplicates (or clears the contents of the cell) - so is the cell was empty anyway it would still be empty after the code has run. It does not delete the cell it just clears the contents of the cell and a cleared empty cell is an empty cell - do you see what I am getting at.


    However also try snb's code as this is shorter and quicker more efficient - to clear the actual cell just extend snb's code to


    There is a difference though - Jindons code removes the duplicates whereas snb's code leaves in the the first value and removes subsequent duplicates (correct me if I am wrong with your intent snb with respect to your code)

  • Re: Code for finding duplicates to ignore empty Cells


    @ [Blocked Image: http://www.ozgrid.com/forum/image.php?u=115597&dateline=1300466393] smuzoen,


    Thank you for your contribution. It seems that we have posted our replies at the same instant.


    In my first post of this Thread, I have posted a link to a thread dealing with not only clearing duplicates, but also the original duplicate value.


    I have tried to implement your alternative options, without success. If you click on the "Delete Duplicates" button a second time round, it messes up the actual data.


    Thank you.:)

  • Re: Code for finding duplicates to ignore empty Cells


    You did not include the specialcells - see attached workbook - I also commented out the deleting of the rows to show the data stays the same if you run the code more than once in Sub cleardups()


    You will have to delete the blank rows when you reset otherwise CurrentRegion will not work. You could use

    Code
    For Each r In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column).SpecialCells(2)

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!