delete rows with multiple criteria
 

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

delete rows with multiple criteria
 

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"

--
Don Guillett
SalesAid Software

"sverre" wrote in message
...
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

delete rows with multiple criteria
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sverre" wrote in message ...
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

delete rows with multiple criteria
 

Hi sverre

Remove Not in this line

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


--

Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message
...
Sorry,
What I would like to to do is the reverse- Delete rows that NOT contain
any
of the names below i column C

EXO_BASKET
EXO_CLI
EXO_EXOTIX
EXO_FLOW
FXSECUR
EXO_OTC_CLI
EXO_OTC_EXOTIX
EXO_OTC_FLOW
EXO_TURBO
EXO_OTC_BASKET


Is that harder to do?
Br
Sverre
"Ron de Bruin" skrev:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sverre" wrote in message
...
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