Whre you have 45000 row the best way of removing items is to put an X in
an auxilary columns for the rows to delete. Then using auto filter get
the visible rows from the autofilter and delete these rows. Remvoing
one row at a time take forever. This will run in a few seconds instead
of minutes.



Code:
--------------------

Sub Removeduplicated()

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data on 4 columns
'do the last column
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("D1"), _
order1:=xlAscending
'do 3 other coluns
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("B1"), _
order2:=xlAscending, _
key3:=.Range("C1"), _
order3:=xlAscending

'Put in column IV an X for columns to delete
For RowCount = 2 To (LastRow - 1)
If .Range("A" & RowCount) = .Range("A" & RowCount + 1) And _
.Range("B" & RowCount) = .Range("B" & RowCount + 1) And _
.Range("C" & RowCount) = .Range("C" & RowCount + 1) And _
.Range("D" & RowCount) < .Range("D" & RowCount + 1) Then

.Range("IV" & (RowCount + 1)) = "X"
End If

Next RowCount

'Make sure the is at least one X is column IV before applying autofilter
Set c = .Columns("IV").Find(what:="X", _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
.Columns("IV").AutoFilter
.Columns("IV").AutoFilter Field:=1, Criteria1:="X"
.Rows("1:" & LastRow).SpecialCells (xlCellTypeVisible)
.Columns("IV").Delete
End If


End With

End Sub

--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=156437

Microsoft Office Help