Need Macro code for Deleting Duplicate rows

[PaigeWarner]

Need Macro that looks to the duplicates in one column only and deletes the whole row of duplicates, but keeping one row.

 

[Joe4]

Welcome to the Board!

Have you tried Excel's built-in "Remove Duplicates" functionality? You can have it work on just one column, but delete the whole row.
If you want the VBA code for that, just turn on the Macro Recorder, and record yourself doing that manually.

 

[PaigeWarner]

Yes, I have tried that but the problem is that it doesn't work because the other columns are all different. So across the whole row it is not a duplicate but just in one column it could have 2 3 or 4 duplicates. I have also tried not expanding my selection and the problem there is it moves everything up in that one column and the rows are no longer correct. I am hoping for a macro that can solve this problem so I don't have to keep going through manually and deleting the duplicate rows.

 

[Joe4]

What column are the duplicates you checking for in?
Which columns need to be deleted (really, just asking which columns have data, since you want to delete the whole row)?

 

[PaigeWarner]

The screen shot below is an example of the type of files I am working on (its fake info- I replaced all real data for privacy).

I've already done Conditional formatting to highlight my duplicates and also alphabetized.

So I need VBA coding to look at Column C and recognize that there are a number of different duplicates and just keep one duplicate and delete the rest.

For example, maybe keep line 2 and delete lines 3 and 4 for Fakeemail1@gmail.com (keep line 5 but delete line 6, keep line 7 but delete line 8. Keep line 9 but delete lines, 10, 11 and 12 etc.).

The duplicate variations and data will be different every time so I am not able to turn on the macro recorder for this.

Thank you in advance for any assistance you can provide! Much appreciated!

 

[Joe4]

The duplicate variations and data will be different every time so I am not able to turn on the macro recorder for this.

The Macro Recorder gives you the base code, and then you may need to generalize it a little.
I am assuming that you will have columns A-L every time, and the email is in column 3 every time.
The only thing "variable" is really where the data ends.

So, I turned on the Macro Recorder, which gave me the code for a set range. Then I just added a line to dynamically find the last row with data in column C, and replaced the ending row number in that line of recorded code with the dynamic last row variable, like this:

Sub DupDeleteMacro()

    Dim lr As Long
    
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row

'   Delete duplicate rows on column C
    ActiveSheet.Range("$A$1:$L$" & lr).RemoveDuplicates Columns:=3, Header:=xlYes
    
End Sub

If I run it on your example, I am left with the header and five rows of data, one for each email address.

 

[PaigeWarner]

Works exactly how I was hoping, thank you so much!

 

[Joe4]

You are welcome!

The Macro Recorder really is a very helpful tool in writing code like this, especially if you are fairly new to VBA (but even us veterans use it too)!
The important thing to remember is that most of the time, it won't give you exactly what you need, but it can give you a good chunk of code to start from.
Many times, it just requires a little fine tuning to make it more dynamic and clean it up a little.

 

[PaigeWarner]

Yes, I have used the Macro recorder many times, it's great. Having to fine tune things is where my skill ends though Thank you again!