Results 1 to 2 of 2

Thread: Fastest method to delete rows (set off)

  1. #1

    Fastest method to delete rows (set off)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

    =OR(AND(A2=A1,B2=B1,SUM(D1:D2)=0),AND(A2=A3,B2=B3,SUM(D2:D3)=0))

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •