vba - Deleting duplicates in excel is too slow -
i have large worksheet many duplicates. duplicates in column e , have delete ones lowest number in column l. i'm using code:
sub duplicados() dim i, lr application.screenupdating = false application.enableevents = false activesheet.usedrange.sort key1:=range("e2"), order1:=xlascending, key2:=range("l2") _ , order2:=xlascending, header:=xlguess, ordercustom:=1 lr = cells(rows.count, "l").end(xlup).row = lr - 1 1 step -1 if cells(i, "e").value = cells(i + 1, "e") rows(i).entirerow.delete end if next application.screenupdating = true application.enableevents = true end sub
however it's slow , wondering if there better way it.
one reason code takes long it's deleting 1 row @ time. 1 thing can speed process of current structure first identify rows need deleted , delete them @ once. see following example way using union
method:
dim rr range dim long dim lr long activesheet.usedrange.sort key1:=range("e2"), order1:=xlascending, key2:=range("l2") _ , order2:=xlascending, header:=xlguess, ordercustom:=1 lr = cells(rows.count, "l").end(xlup).row = lr - 1 1 step -1 if cells(i, "e") = cells(i + 1, "e") if rr nothing set rr = cells(i, 1) else: set rr = union(rr, cells(i, 1)) end if end if next rr.entirerow.delete
Comments
Post a Comment