1

I'm currently trying to create a loop that will look at column C starting from row 5 and compare each cell in that column until it reaches the last used cell in that column. Each cell would be checked against 8 variables to see if it matches. If the cell doesn't match any of variables the entire row must be deleted.

My current attempt looks like: 

Dim AC as long
Dim LastRow as long
AC=5
LastRow= Activesheet.range("A" & Rows.count).end(xlup).row
For AC = 5 To LastRow
            With Cells(AC, "C")
            Do Until Cells(AC, "C").Text = OC1 Or Cells(AC, "C").Text = OC2 Or Cells(AC, "C").Text = OC3 Or Cells(AC, "C").Text = OC4 Or Cells(AC, "C").Text = NC1 Or Cells(AC, "C").Text = NC2 Or Cells(AC, "C").Text = NC3 Or Cells(AC, "C").Text = NC4
                Rows(AC).EntireRow.Delete
            Loop
        End With
    Next AC

This should insure that once a row has been deleted the new row that took it's place (Ex. Deleting the entire row 5 would result in row 6 becoming row 5) So it should exit the Do Loop when there is a match, grab the next line number and repeat until there is another match. Only the code keeps throwing an execution interrupted error. Can someone please tell me what I'm doing wrong?

user7898186
  • 13
  • 3

1 Answers1

0

If your code is causing an infinite loop, and your error is only being generated when you try killing the infinite loop, you could use the following code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim AC As Long
Dim LastRow As Long
AC = 5
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Do While AC <= LastRow
    If Cells(AC, "C").Text <> OC1 And _
       Cells(AC, "C").Text <> OC2 And _
       Cells(AC, "C").Text <> OC3 And _
       Cells(AC, "C").Text <> OC4 And _
       Cells(AC, "C").Text <> NC1 And _
       Cells(AC, "C").Text <> NC2 And _
       Cells(AC, "C").Text <> NC3 And _
       Cells(AC, "C").Text <> NC4 Then
        Rows(AC).Delete
        LastRow = LastRow - 1
    Else
        AC = AC + 1
    End If
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

The problem with the way you were currently doing things is that, once you got near LastRow (assuming you had deleted any earlier rows), you were looking at blank rows and therefore infinitely deleting them.

Or, of course, you could use the more generally accepted way of deleting rows - which is to start at the bottom and work upward:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim AC As Long
Dim LastRow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For AC = LastRow To 5 Step -1
    If Cells(AC, "C").Text <> OC1 And _
       Cells(AC, "C").Text <> OC2 And _
       Cells(AC, "C").Text <> OC3 And _
       Cells(AC, "C").Text <> OC4 And _
       Cells(AC, "C").Text <> NC1 And _
       Cells(AC, "C").Text <> NC2 And _
       Cells(AC, "C").Text <> NC3 And _
       Cells(AC, "C").Text <> NC4 Then
        Rows(AC).Delete
    End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • LastRow should be calculating the last used row no the worksheet, I believe? It also is erroring out immediately and when I step through it, it's still on the first line (row 5) – user7898186 Apr 20 '17 at 21:56
  • `LastRow` is being calculated as the row containing the last non-empty cell in column A. What error is the code generating? (It worked for me, but maybe there is a difference between my test data and your actual data that is causing it problems.) – YowE3K Apr 20 '17 at 22:04
  • I swapped my code for yours and it appears to be working now. There is an insane amount of data it has to go through, so it'll have to run for a while. You're code is extremely clean compared to mine, but I still feel like mine should have succeeded...which not understanding why bothers me more than my code not working lol – user7898186 Apr 20 '17 at 22:14
  • Nope...It got down to the last row being 133088 and then threw the same error as before: Code execution has been interrupted – user7898186 Apr 20 '17 at 22:29
  • Your code would have gone into an infinite loop once it started processing the blank rows that would have moved up into the bottom of your range, e.g. if `LastRow` was 1000, and then you deleted row 20, the previous row 1001 would move into row 1000, so when you went to process row 1000 it would be looking at empty data. – YowE3K Apr 20 '17 at 22:45
  • I always though that "code execution has been interrupted" was shown when the user interrupted the code (i.e. Ctrl-Break, or Esc). It seems a strange error message for the code itself to generate. – YowE3K Apr 20 '17 at 22:47
  • On the 'it'll have to run for a while' comment: you may have already done this but make sure you turn off 'screen updating' at the start of your code and back on again at the end. If you haven't already done that it'll run much more quickly that way. – Steve Lovell Apr 20 '17 at 23:04
  • And switch calculation to Manual. Thanks @SteveLovell for pointing that out - I do that so instinctively that I forget that other people don't (especially when it is only a code snippet and not the entire subroutine). – YowE3K Apr 20 '17 at 23:06
  • @user7898186 - Make sure that you have `ScreenUpdating` and `Calculation` set correctly while doing the deletes - see my latest amendments to the answer. – YowE3K Apr 20 '17 at 23:11
  • I'm not at my machine to test (posting from my phone), but wonder if other external factors could also cause that error. For example if the file is on a network drive and there was a network blip, or during the process the file got locked by a backup process. The longer it runs the more likely such things might be. Pure speculation on my part though. – Steve Lovell Apr 20 '17 at 23:11
  • @SteveLovell - I don't think network issues would be a cause, because Excel would be working on the workbook in memory, but certainly there are a lot of things that could happen locally that might impact it. – YowE3K Apr 20 '17 at 23:16
  • 1
    This post and answer may be useful: http://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted – Steve Lovell Apr 21 '17 at 08:19
  • Thank you both! It was a combination of the code, and the breakpoint issue that Steve provided the link for. After letting the code run for a half hour it successfully completed what I was looking for. Now on to the next step, thanks guys! – user7898186 Apr 21 '17 at 14:56