excel vba - VBA - Delete rows that have the same ID and based on a Date -

excel vba - VBA - Delete rows that have the same ID and based on a Date -

i trying build vba macro in order delete rows have same id and contains date before 01/01/2015. please see screenshot : http://i61.tinypic.com/2wpjno3.png (red rows have deleted).

i have started build next macro :

sub auto_open() application.screenupdating = false application.calculation = xlcalculationmanual '***** variables declaration ***** dim lastrow integer dim eventdate string dim col new collection dim itm dim long dim cellval variant '***** find lastly row ***** lastrow = range("a" & rows.count).end(xlup).row '***** conditional formatting statut ***** = 2 lastrow cellval = sheets("dataset1").range("a" & i).value on error resume next col.add cellval, chr(34) & cellval & chr(34) on error goto 0 next each itm in col debug.print itm next application.calculation = xlcalculationautomatic application.screenupdating = true end sub

unfortunately lists different ids , don't know how select rows want delete.

could advise?

thank you, damien

why don't store row number i when finding row delete?

col.add cellval, chr(34) & cellval & chr(34),

actually, you're half way through. in loop, determine if id , date match pattern, , if so, store rownumber. in sec loop, looping end start (step -1), delete each row in collection.

edit: maintain things simple. not utilize references objects, objects. "make work, create right, create fast" - in order. remains code when necessary:

row = 2 lastrow celldate = cdate(s.cells(i, 2).value) if (celldate < testdate) if not d.exists(s.cells(i, 1).value) d.add s.cells(i, 1).value else ' duplicate! willdeleted.add row end if end if next row row = willdeleted.count 1 step -1 rows(willdeleted(i)).entirerow.delete next row

and not utilize dictionary row numbers (willdeleted)! simple list do, either array() or collection. dictionaries not maintain order in elements added. list of rownumbers delete must sorted, lowest highest, in order able delete rows bottom of table up.

vba excel-vba