Finding Duplicate Values using VBA

[cunnin]

Hi,

I have a question I'm hoping to get help with! I have multiple columns / rows of data, some of which are duplicates.

Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.

I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.

So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.

I'm hoping that this is easy to do and greatly appreciate any support / help I can receive!

Many thanks in advance, cheers

 

[diddi]

hi, this is a fairly simple solution which you can modify if you need. note it steps backwards because if you work downwards and delete rows you mess up the row counter when deletions are made. warning work on a copy of your workbook when trialling deletion subs

Sub DelDupes()
    For row=Cells(Rows.Count, "A").End(xlUp).Row to 2 step -1
        If cells(row,19).value=cells(row-1,19).value then
            Rows(row).Entirerow.delete
        end if
    next row
end sub

 

[cunnin]

Hi!

Thanks very much for this! I added this into my worksheet and it seems like it may do what I need it to!

Quick question though - my data starts on row 8, and subsequently the VBA code seems to be deleting the "duplicate" blanks cells from rows 1 - 7. What would be the best way to get around this? ie I want it to stop when, working from the bottom, it hits the top cell where the data begins.

Hopefully this makes sense

Many thanks again for all your help!

Cheers

 

[farmerscott]

Hi Cunnin,

Try changing Diddi's code to-

Sub DelDupes()
For row=Cells(Rows.Count, "A").End(xlUp).Row to [COLOR=#ff0000]8[/COLOR] step -1
If cells(row,19).value=cells(row-1,19).value then
Rows(row).Entirerow.delete
end if
next row
end sub

FarmerScott

 

[hasski]

I don't mean to be picky but doesn't this hinge on :
a) the data being sorted (as you're only comparing consecutive rows)
b) that a value is not repeated more than once; multiple repetitions are not trapped
.. or am I missing something?

 

[farmerscott]

Hasski, you are correct.