Combine deletion Criteria
 

Hello,

I have to delete rows meeting any of several criteria.
The way I have it now, It searches the same list several times.

Can the following be combined somehow???

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)

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

Combine deletion Criteria
 

I'm not sure I quite understand what you are trying to do but I think you
should be able to do this...

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

"gmunro" wrote:

Hello,

I have to delete rows meeting any of several criteria.
The way I have it now, It searches the same list several times.

Can the following be combined somehow???

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)

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

Combine deletion Criteria
 

Great, thanks