Remove duplicate info

  • Thread starter Thread starter Mags
  • Start date Start date
M

Mags

Hi
I am new to using Excel 97. I have a speardsheet that has
19000 rows. Column A contains a list of numbers. Some of
these numbers are duplicates. Is there a way to remove the
duplicate entries?

Thank you
Mags

Column A
12345
12345
12345
54321
54321
 
Mags,

I just posted this code for someone else, but I think it works for you as
well

Sub DeleteDuplicates()
Dim cLastRow As Long

With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1").FormulaR1C1 = _
"=IF(COUNTIF(R2C1:RC1,RC[-1])>1,""Duplicate"","""")"
.Range("B1").AutoFill Destination:=_
.Range("B1", .Cells(cLastRow,"B")), Type:=xlFillDefault
.Range("A1").EntireRow.Insert
.Range("B1").FormulaR1C1 = "Test"
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Duplicate"
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
See one answer in microsoft.public.excel.

Please don't post the same message in multiple groups. It tends to
fragment any replys you get, and it potentially wastes the time of those
replying to a post that's already been answered.
 
Back
Top