0

I have a loop that goes through ~15K rows, checks if a value exists in an array and deletes the row if not. I have been using this loop for months with no problem. Now, each loop is taking approx 2 seconds and I have no idea why. I have done the usual "set calculations to manual" and "turn off screen update" settings with no improvement.

Sorry but the data is confidential so I can't give you guys the source files

        For lngCounter = lngLastrowresults To 3 Step -1
        On Error GoTo Handler
           If IsError(Application.Match(Range(Mid(strProjLoc, 2, 1) & CStr(lngCounter)), arrProjectID, False)) Then
             Range("B" & CStr(lngCounter)).EntireRow.Delete
             Debug.Print lngCounter
           End If
         Next lngCounter

Any ideas?

Update1: I closed out of Excel and reopened and started the loop again. It began running quickly but is getting slower time.

Update2: I isolated the loop and have been F8ing through the lines. The line that stalls is "Range("B" & CStr(lngCounter)).EntireRow.Delete". It seems to be taking a long time to actually delete the row.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Chris
  • 31
  • 8
  • can you clear the row instead of deleting it? – Any Moose Sep 11 '18 at 01:32
  • I didn't think about testing .clear instead of .delete but I found a solution. Thanks for the idea – Chris Sep 11 '18 at 01:41
  • Possible duplicate of [VBa conditional delete loop not working](https://stackoverflow.com/questions/19241905/vba-conditional-delete-loop-not-working) – Mitch Wheat Sep 11 '18 at 01:42
  • I don't believe it's a duplicate because that problem was with the bad loop design. It appears my issue with the time it takes .Delete to execute – Chris Sep 11 '18 at 02:03

1 Answers1

0

So I have found a different method of deleting the rows that is MUCH faster. I used this method described here and it works great. https://stackoverflow.com/a/19241990/10344640

Thanks Siddharth Rout for posting this!

Chris
  • 31
  • 8