amolwakchaure
New member
- Joined
- Sep 18, 2014
- Messages
- 1
- Reaction score
- 0
- Points
- 0
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.
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 |