Delete rows where cells contain various criteria

[gmunro]

Hello,

I am working on a macro that will delete a varying number of rows based
on certain criteria.
Every row is listed twice.

What I do now is sort the columns to put all the duplicates together
and have a simple if formula to put a 1 next to every row on it's
second occurance.

What I need to do is
Delete all of the rows where a 1 occurs in column A
Delete all the rows where "CA_" appears at the left of column B
Delete all Rows where "CA-" appears ANYWHERE in the value of column B
Delete all rows where "Undef_" appears on the left of Col B
Delete all rows where "Target_" appears on the left of Col B

I currently having these as separate steps which is adding a lot of
time to the macro, and actually shut my computer down yesterday while
running it

Is there a way to combine the deletion crieteria to make it easier?

Here is some of my code:

Range("B27").Activate
Do
If Left(ActiveCell.Value, 3) = "CA_" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)
ActiveCell.Offset(-2, 0).Select
Do
If Left(ActiveCell.Value, 6) = "Undef_" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

ActiveCell.Offset(-2, 0).Select
Do
If Left(ActiveCell.Value, 7) = "Target_" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

and


With ActiveSheet
.Rows(1).Insert
.Range(MYCOL & 1).Value = "Temp"
.UsedRange
With Intersect(.Columns(MYCOL), .UsedRange)
.AutoFilter Field:=1, Criteria1:="*CA-*"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.UsedRange
End With

and

Range("B27").Select
Do
If Left(ActiveCell.Value, 1) = 1 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

Any help would be appreciated,

Glen

 

[Guest]

gmunro,

Without rewritting your code (or really thinking too much =p sorry i'm
lazy this morning.)


If Left(ActiveCell.Value, 3) = "CA_" _
or left(activecell.offset(2,0).value,6)="Undef_" _
or Left(ActiveCell.offset(4,0).Value, 7) = "Target_" _
Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)