Delete rows where cells contain various criteria

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

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)


"gmunro" wrote:

> 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
>
>