Deleting Duplicates

C

Charles Deng

I have a huge data set on Excel. I would like to know
what programming codes (Macro)I can use to delete those
duplicate records on a specific column, such as column F.
Any help will be highly appreciated.


Charles Deng
 
T

Tom Ogilvy

Flag the records using Countif, then filter on the duplicates and delete.

Turn on the macro recorder while you do it manually to get the code.
 
S

steveb

Charles,

I have found the easiest was to use the Filter > Advanced Filter and select
unique records.
Just played with this - selected the entire column - Filter > Advanced
Filter - no criteria - unique records - filter in place - and it worked like
a champ. Now I can copy/paste.

Record a macro and you'll get the code you need.

Here's a discussion you might want to read...
http://www.excelforum.com/t166812-s0a08f6df3bf8276146dd91f42a75a4ca

hth
 
B

Bob Phillips

Hi Charles,

Here is some code

Sub filterData()
Dim cRows As Long
Dim rng As Range
Dim testFormula As String

Columns("G:G").Insert
Rows(1).Insert
Cells(1, "G").Value = "Temp"


With ActiveSheet
cRows = .Cells(.Rows.Count, "F").End(xlUp).Row
testFormula = "=IF(COUNTIF(F$2:F2,F2)>1,""Y"" ,"""")"
'create a test formula
.Cells(2, "G").Formula = testFormula
'copy the formula down all rows
.Cells(2, "G").AutoFill Destination:=.Range(.Cells(2, "G"), _
.Cells(cRows, "G"))
Set rng = .Range("G:G")
rng.AutoFilter Field:=1, Criteria1:="Y"

End With

rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Columns("G:G").Delete
Rows(1).EntireRow.Delete

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top