Delete rows using duplicates from one column macro code issue

[Ianmacros]

Hi, I am currently trying to write a code to select all data and then delete all rows based on duplicates from a single column. I want to delete rows based on duplicates in columns A, J, E, and K. I typically do this by hand one column at a time using the Remove Duplicates tool in Data tab. However, I am trying to write a code since this is a repetitive process. The code as it currently exists seems to delete duplicates using column F four times rather than doing 4 different columns (A, J, E and K).

Please Login or Register  to view this content.

[KOKOSEK]

Try like this:

Please Login or Register  to view this content.

[Ianmacros]

Thank you for the response. From my understanding, this code will look at every column from A to Q and then remove duplicates based on every column. Is that correct? For my task, I will only want to check columns A, J, E and K for duplicates and no others.

[KOKOSEK]

Thank you for the response. From my understanding, this code will look at every column from A to Q and then remove duplicates based on every column. Is that correct? For my task, I will only want to check columns A, J, E and K for duplicates and no others.

Sorry, I've sent it before adapt for specification :-)

Put instead of iCol loop:

Please Login or Register  to view this content.

[Ianmacros]

No problem!

When I test the code, it endlessly loops through columns A, J, E and K selecting them but not deleting the duplicates. Not sure why.

[KOKOSEK]

Hmm it works for me.

Please Login or Register  to view this content.

[Ianmacros]

My thought exactly. I had to add in one line of code, which I think was just accidentally left off. The 'Select Range part of the original code. Seems to work now. Appreciate the help!

Please Login or Register  to view this content.

[KOKOSEK]

You welcome.
If you happy with solution please use Thread tools and mark thread as SOLVED.

[Ianmacros]

OK so upon a closer look it is very close but needs one more small tweak. Right now, it is just deleting the cell with the duplicate. I want the entire row to be deleted if a duplicate cell is found.

[Arkadi]

Odd Ianmacros, your original code does exactly what you want it to do when I run it on my end. After running it, do you still have duplicates in A E J or K?

[Ianmacros]

Arkadi, the duplicate values are deleted but the rows in which they exist remain.

[Arkadi]

I meant your FIRST post, not the replies. Yes I know the reply would not delete the whole row, but your first code should.

[Ianmacros]

Oh sorry I understand. The first code brought up delete duplicates window but it looks like it highlights column K and loops four times rather than for the 4 different columns I thought I specified (A, J, E, K). However, in the data, it looks like it does what it should do. Any thoughts?

[Arkadi]

Not sure why it looks like column K would look highlighted, in theory the whole range should be but yes the last column would be K maybe that's why? Or are you stepping through the code and it does it from the start?

Either way there is no need to select the range before removing duplicates, you are specifying the applicable range in the removeduplicates commands.

On my end the results with your code are as expected, and the whole range gets selected, and I don't see any column looking highlighted at all.

[Ianmacros]

I think I am not being clear here. The entire data range is selected. Correct. However, when the remove duplicates dialog pops up, it does so 4 times. I deleted the last 3 rows (see below) and even then it pops up 4 times when just checking column A.

Please Login or Register  to view this content.

[Arkadi]

Weird, I never even get a dialog popping up when removing duplicates via code.
Any chance you have a worksheet_change event running that would trigger this macro to run multiple times?

If you could attach the file with all existing code (some sample data would be handy but not so important in this case), maybe we can see more of what is going on.

[Ianmacros]

Arkadi, I think the code I am using is just not equivalent to what I would do by hand. When I replicate what the code does by hand, it's different than what I actually do by hand. I'll just keep doing this manually. I don't think my understanding of VBA is strong enough to code this even with your help. Thanks for your patience and help!

[Arkadi]

I believe if I understood correctly, the code should be doing the same as you are doing manually... i've automated the same in a few programs I have. However, it is your decision. If you decide to pursue it further, feel free to ask questions or provide a sample workbook with input and expected output.

We are always happy to help