Delete Duplicate Records

Originally Posted by Cjax

If I browse the forums just to learn techniques should I start a new thread everytime I have a question about an author's answer. I didn't think trying to understand a thread was the same as having my own situation i need help with.

Of course, you are correct. I apologize.

And one of the things i *really* need to do better is put comments into the code itself to explain what's going on.

Ok, let's start with your original question (which is what sounded like a topic change to me...)..."how to add a macro".

==========
How to use add a macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
==========

If that's all you needed, great. If not, we can talk about the specifics of the macro itself. Here's the second macro with more "notes" added:

Sub DeleteDupesOnly()
'Deletes duplicates leaving complete list of unique values only

'Declare a "last row" variable, always use "Long" for row variables
    Dim LR As Long

'Data needs to start at row 2, so we'll insert a row
    Rows(1).Insert (xlShiftDown)

'Put a value into the LR variable, this tells us how for down the data goes
    LR = Range("B" & Rows.Count).End(xlUp).Row
    
'Insert a formula in column AA starting at AA2 =COUNTIF($AA$2:AA2,AA2)
'This will give a count of how many times the value in column A has occurred
'so far in the column down to this row, duplicates will have higher and higher
'numbers as the formulas go down that column
    Range("AA2:AA" & LR).FormulaR1C1 = "=COUNTIF(R2C2:RC2,RC2)"

'Insert an Autofilter in column AA and show all rows with values
'greater than 1. This will be all the duplicates after the first
'instance of each value
    Range("AA1") = "Key"
    Range("AA1").AutoFilter Field:=1, Criteria1:=">1"

'Delete the visible rows, since the unique values have been hidden this is OK
    Range("AA2:AA" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
'Cleanup, remove the column AA we added and the row 1 we added
    Rows(1).Delete (xlShiftUp)
    Columns("AA:AA").Clear
End Sub