delete rows with multiple criteria

[Guest]

Hi

I would like to delete all rows that contain these names in col C

EXO_BASKET
EXO_CLI
EXO_EXOTIX
EXO_FLOW
FXSECUR
EXO_OTC_CLI
EXO_OTC_EXOTIX
EXO_OTC_FLOW
EXO_TURBO
EXO_OTC_BASKET

How do I write this code?

Many thanks for the help

Br
Sverre

 

[Don Guillett]

you can use a for/next loop

if cells(i,"c")="a" or cells(i,"c")="b" then rows(i).delete

or a select case statement
case is "a", "b", "c"

 

[Ron de Bruin]

Hi Sverre

Another option is to enter the names in another sheet and use Match
I use a sheet named "Sheet2" with in A1:A20 the values you want to delete

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsError(Application.Match(.Cells(Lrow, "C").Value, _
Sheets("Sheet2").Range("A1:A20"), 0)) Then .Rows(Lrow).Delete

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

More information here
http://www.rondebruin.nl/delete.htm