Excel: Delete Rows Based on different Criteria in Multiple Loops


Hey all. I am trying to delete rows based on several different criteria. I have used a basic format a few times to delete several different criteria. I got this code on the forum about a year ago. To use it I would simply change the criteria and just call a few of these procedures back to back to get the result I wanted. However, I am trying to learn how to do this in a loop all in one procedure to be more efficient. Here is what I have used in the past. I would just substitute out the criteria as needed:


Sub Delete_WO()
Dim lr As Long, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
    If Range("A" & i).Value = "W/O#" Then Rows(i).Delete
Next i
End Sub
I have attempted to complete the task but have had no luck. I want to "look" in column C first and find out if the value "Template" exists. If this is the case I want the whole row deleted. THen I want to do the same thing with column B. First with values that are like "BB*" and then with values like "AA*" THese values actually occur like this AA-##### or BB-###### (ex. AA-45678 or BB-54657) Here is what I have currently:

Sub DelTemplate()
Dim  lr    As Long
Dim  i     As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
    If Range("C" & i).Value = "Template" Then Rows(i).Delete
Next i
Set lr = Range("B" & Rows.Count).End(xlup).Row
    If Range("B" & i).Value Like "BB*" Then Rows(i).Delete
Next i
 
    If Range("B" & i).Value Like "AA*" Then Rows(i).Delete
Next i
End Sub
Any help you could provide would be incredible!!! Thank you.


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of June 19, 2014.


For more resources for Microsoft Excel