Help with VBA If statement with multiple criteria

[vrobinson0304]

Hi, I am attempting to create a macro that scans a column and if the word "Watch" is found and the row above "Watch" is blank, it deletes the entire blank row. The code I tried is below, but doesn't work. Any help would be greatly appreciated! Thank you

For i = 1 To Cells(Rows.Count, 13).End(xlUp).Row<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
If Cells(i, 2) = " Watch" And Cells(i,2).Offset(-1,0) = "" Then<o:p></o:p>
Rows(i).Offset(-1,0).EntireRow.Delete Shift:=xlUp<o:p></o:p>
End If<o:p></o:p>
Next i

 

[Peter_SSs]

What about your code doesn't work? Nothing happens? Deletes the wrong row? etc

Could it be that your code is checking for a space before the word? That is " Watch" not "Watch"

Which column would "Watch" be in? Your code seems to use column 2 (B) and column 13(M), why is that?

Could "Watch" occur more than once in the column?
Could there be more than one row to delete?

 

[vrobinson0304]

I receive the "Run-time error '438':

Object doesn't support this property or method.

And the line If Cells(i,2)... is highlighted in the debugger.

There are supposed to be three spaces before the word Watch. Watch would be in column 2 (B) and it uses column 13 (M) because that is the column that would have the most rows. Watch would not occur more than once. There would only be one row to delete.

Thanks !

 

[Rick Rothstein]

For i = [SIZE=3][COLOR=darkred][B]1[/B][/COLOR][/SIZE] To Cells(Rows.Count, 13).End(xlUp).Row<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
If Cells(i, 2) = " Watch" And Cells([SIZE=3][COLOR=darkred][B]i[/B][/COLOR][/SIZE],2).[SIZE=3][COLOR=darkred][B]Offset(-1,0)[/B][/COLOR][/SIZE] = "" Then<o:p></o:p>
Rows(i).Offset(-1,0).EntireRow.Delete Shift:=xlUp<o:p></o:p>
End If<o:p></o:p>
Next i

When your 'i' variable is 1, that Offset property will try to look at Row 0... there is no Row 0 however.

 

[vrobinson0304]

Gotcha. What code would set the criteria such that if the a cell in column B contains " Watch" AND the row above it is blank, to delete the blank row?

Thanks!

 

[Rick Rothstein]

Gotcha. What code would set the criteria such that if the a cell in column B contains " Watch" AND the row above it is blank, to delete the blank row?

Your code appears to be fine (although my personal preference is to specify the .Value property rather than relying on it being interpretted as the default property), just change the i=1 to i=2 in the For statement.

 

[Peter_SSs]

I'm sure starting your loop at row 2 will do what you want - and most likely quickly enough, but a couple of comments that you might want to consider for the future, if not for this exercise.

1. Using column M to find the last row for this loop is not necessary and is likely to just make your code loop extra times for nothing. Clearly " Watch" cannot occur in column B after the last entry in column B, so if column B had 100 rows and column M had 10,100 rows checking those last 10,000 rows would be wasted.

2. Since " Watch" can only occur once in the range, checking every row could also be pointless. For example, if column B had 10,100 rows and " Watch" was found in row 100 then again the last 10,000 checks would be pointless.

So, an alternative would be this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Row_Above_1()<br>    <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> Found = Range("B2", Range("B" & Rows.Count).End(xlUp)).Find _<br>        (What:="   Watch", LookIn:=xlValues, LookAt:=xlWhole, _<br>        MatchCase:=False, SearchFormat:=False)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Found.Offset(-1).Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            Found.Offset(-1).EntireRow.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


If you happen to have a lot of rows (tens or hundreds of thousands), then this would be a faster again.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Row_Above_2()<br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bStop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    lr = Range("B" & Rows.Count).End(xlUp).Row<br>    a = Range("B1:B" & lr).Value<br>    i = 1<br>    <SPAN style="color:#00007F">Do</SPAN><br>        i = i + 1<br>        <SPAN style="color:#00007F">If</SPAN> a(i, 1) = "   Watch" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> a(i - 1, 1) = "" <SPAN style="color:#00007F">Then</SPAN><br>                Rows(i - 1).Delete<br>                bStop = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> bStop And i < lr<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[vrobinson0304]

Thanks Peter. You saved the day.