Fastest method to delete rows (set off)

[amolwakchaure]

Hi, Need help to delete rows (set off summary of records which are not matching) in same Business Unit same Department and same Amount(sum should be 0) and Reference should be start with (left 1 character) 1 and L. i already have a macro in place with While Wend loop but it takes more than 4 hours for deleting 50K rows. i read it in forum that collections/Array can do this very fast but i am not much aware of Collection/Array.
e.g. if we select first row which is 301, 900, 1*, -27137.64 will have to loop through remaining 3 records of department 900 if found will delete both rows. also just to speed up process will have to skip records which are already searched and matched or not matched and delete all matched rows at the end of process by using Union.

Business Unit	Department	Reference	Amt
301	900	14135-CBP98	-27137.64
301	900	LO078195771400	27137.64
301	900	LO078095807000	-518974.5
301	900	14135-CBP98	37205.43
301	0CB	14134-XFQ17	-23948000
301	0CB	LO078150991400	23948000
301	0GD	LO078110945500	1500000
301	0GD	14133-SWX32	-4500000
116	0NG	14104-YSW92	18717.93
116	0NG	LO077199301100	-18717.93
116	0NG	14122-IKY84	31263.54
116	0NG	LO077773408800	-31263.54
116	0NG	LO077609295800	-27137.64
116	0NG	14133-RGY42	27137.64

 

[Bob Phillips]

I would add a helper column with a formula starting in row 2 of

=OR(AND(A2=A1,B2=B1,SUM(D12)=0),AND(A2=A3,B2=B3,SUM(D23)=0))

then autofilter that column for FALSE and delete all the rows.