If statement with multiple conditions, how to fix

Question

I want to be able to parse through a worksheet and remove certain rows based on criteria "Logged Off" being in one of the cells. The trick is, it can't be every instance of it, just the ones where the next row shows one of the parameters of the 'status' array. The approximate size of the worksheet is 4 columns and roughly 10000~ rows.

Dim firstRow As Long
Dim nextRow As Long
Dim currentDate(1 To 5) As String
Dim totalDelete As Long
Dim p As Integer
Dim i As Integer
Dim status(1 To 5) As String

status(1) = "Available"
status(2) = "Email"
status(3) = "Available, No ACD"
status(4) = "Aux, Technical Issues"
status(5) = "Aux, Client Callback"

currentDate(1) = Sheets("Cover").Range("E12")
currentDate(2) = Sheets("Cover").Range("F12")
currentDate(3) = Sheets("Cover").Range("G12")
currentDate(4) = Sheets("Cover").Range("H12")
currentDate(5) = Sheets("Cover").Range("I12")

firstRow = 2
nextRow = 3
totalDelete = 0

For i = 1 To 5
    For p = 1 To 5

        Do While firstRow <= 10000
            If Cells(firstRow, "C") = "Logged Off" And Cells(nextRow, "C") = status(p) And Cells(nextRow, "B") = currentDate(i) Then
                Rows(firstRow).Delete
                totalDelete = totalDelete + 1

            Else
                firstRow = firstRow + 1
                nextRow = nextRow + 1
            End If

        Loop
        Debug.Print currentDate(p)
        Debug.Print status(p)

    Next p

Next i

Debug.Print totalDelete

Now what I'm expecting is it to loop through about 10000 rows and check for what I described above. It goes through a couple loops to check for all possible dates and status' in the two arrays that I've had. The debugs in the code are just me checking to see if the status and currentDate are outputting correctly, and they are. That leads me to believe something is probably wrong with my IF statement. However, I'm not the most adept at this, so I'm just failing to see where I'm going wrong.

   A    |  B        |  C          |  D 

1 data  | 3/25/2019 | Logged Off  | data

2 data  | 3/25/2019 | Logged Off  | data

3 data  | 3/25/2019 | email       | data

4 data  | 3/25/2019 | email       | data

So after running the code, I would expect it to remove row 2 at the very least.

Answer 1

There's a couple of issues in your code.

1) When you plan to delete rows in a loop, work backwards.

Imagine that you hit row 3 and you have to delete it (firstRow = 3). You delete it and now Row 4 is Row 3 and you iterate firstRow to 4. Essentially the row that WAS row 4 (and is now Row 3) is skipped and isn't checked.

Instead

 FirstRow = 10000
 Do While firstRow >= 2 

and decrement firstrow in each loop:

firstRow = firstRow - 1 

to insure you don't pull the rug out from under yourself. That may or may not fix the issue you are seeing, but it's a definite bug in your code.

2) your loops inside loops

That's 5 x 5 x 10000 loops or 250000 loops. That's pretty aggressive. Instead just loop your 10000 times and test like:

If Cells(firstRow, "C") = "Logged Off" And inStr(1, Join(status, "|"), Cells(firstRow+ 1, "C").value, 1) And InStr(1, Join(currentDate, "|"), Cells(firstRow + 1, "B"), 1) Then

You can just that Join() function to turn the array into a single string delimited by | between each element. Instr() then tests to see if your cell value is IN that String. We set the last parameter of Instr() to 1 so that it's not case sensitive. 10000 loops will be much faster.

3) You don't need the nextRow variable (although this is just nit-picky so you can ignore if you are married to that thing.)

Instead use Cells(firstRow + 1, "C") or Cells(firstRow, "C").Offset(,1) to do that check. Less variables to increment and track this way.

Here's the rewrite:

Dim firstRow As Long
Dim currentDate(1 To 5) As String
Dim totalDelete As Long
Dim status(1 To 5) As String

status(1) = "Available"
status(2) = "Email"
status(3) = "Available, No ACD"
status(4) = "Aux, Technical Issues"
status(5) = "Aux, Client Callback"

currentDate(1) = Sheets("Cover").Range("E12")
currentDate(2) = Sheets("Cover").Range("F12")
currentDate(3) = Sheets("Cover").Range("G12")
currentDate(4) = Sheets("Cover").Range("H12")
currentDate(5) = Sheets("Cover").Range("I12")

firstRow = 10000
totalDelete = 0


Do While firstRow >= 2
    If Cells(firstRow, "C") = "Logged Off" And inStr(1, Join(status, "|"), Cells(firstRow+ 1, "C").value, 1) And InStr(1, Join(currentDate, "|"), Cells(firstRow + 1, "B"), 1) Then
        Rows(firstRow).Delete
        totalDelete = totalDelete + 1                    
    End If
    firstRow = firstRow - 1  
Loop

Debug.Print totalDelete