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

Popular posts from this blog

Android layout hidden on keyboard show -

google app engine - 403 Forbidden POST - Flask WTForms -

c - Why would PK11_GenerateRandom() return an error -8023? -