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

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.

--
Regards,
Tom Ogilvy

"Charles Deng" wrote in message
...
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

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-s0...dd91f42a75a4ca

hth
--
steveb
(Remove 'NOSPAM' from email address if replying direct)
"Charles Deng" wrote in message
...
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

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.Dele te

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)

"Charles Deng" wrote in message
...
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