That is what it does, but then it deletes those rows containing #NA (which
are the duplicates) and removed the inserted column C so you data is back
the way you had it.

I can't say why it is hanging in your situation. I copied it from a general
module where it was working fine for me.

you can remove the

rng1.Select

that is left over from when I was testing it, but that would only stop the
highlighting of the rows to be deleted.

I tested it with 9000 rows, about 8950 to be delete and it took 114 seconds
using this version.

Sub Tester9()
Dim dStart As Double
dStart = Timer
Application.Calculation = xlManual
Dim rng As Range, rng1 As Range
Dim rng3 As Range
With ActiveSheet
Set rng3 = .Range(Range("A1"), .UsedRange)
.Columns(3).Insert
rng3.Columns(3).Formula = _
"=If(Countif($B$1:B1,B1)1,NA())"
rng3.Columns(3).Formula = rng3.Columns(3).Value
On Error Resume Next
Set rng = .Columns(3).SpecialCells(xlConstants, xlErrors)
On Error GoTo 0
If Not rng Is Nothing Then
Set rng1 = Intersect(rng.EntireRow, Range("A:F"))
rng1.Select
rng1.Delete Shift:=xlShiftUp
End If
.Columns(3).Delete
End With
Application.Calculation = xlAutomatic
Debug.Print Timer - dStart
End Sub

Are you sure it is hung or it it just working.

--
Regards,
Tom Ogilvy



bkbri wrote in message
...
Hi Tom,

Just tried your Macro and it doesnt work. What happens is it highlights
all rows that it finds with dublicated text in column b then pushes
c,d,e over 1 and creates its own column in C and adds #N/A beside
column B dublicates and the rest it adds FALSE.

After that my Excel program hangs. Im using Excel 2002.

The way I added in your Macro is I went to tools, macro, visual basic
editor, insert, module. Are these steps correct Im a newbie.

Thanks,
Brian


---
Message posted from http://www.ExcelForum.com/