Hard ONE!!!! - Dubble Conditional row delete code issue.

[rkol297]

I have data in columns A through W with the number of rows varying with each run of the report which means the report could have 500 - 4000 rows each time it runs. What I'm trying to do is look at column "I" and keep any row that the value in column "I" ending with "-0". This is the code I have figured out and it works if I just want to keep values ending in "-0".

Dim CelMEDCO0F As Range, RngMEDCO0F As Range, iMEDCO0F As Long
Set RngMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iMEDCO0F = RngMEDCO0F.Count To 1 Step -1
If RngMEDCO0F(iMEDCO0F).Value Like "*-0*" _
Then RngMEDCO0F(iMEDCO0F).EntireRow.Delete
Next iMEDCO0F

However, I need to also keep rows that contain specific values in column "E" and these rows may have a value in column "I" that does not end in "-0" which is OK i still need to keep the row.
The specific values in column "E" that I must keep are:
ARAL, BERT, EPAC, EPAP, EPOP, FL50, F100, GLSA, REMO, REMP, RMIP, RMIV, VENP, VENT, ZEMA, ZYME, ZYMF

So i need to keep all rows that have the above specific values in column E regardless of what the value in Column I ends in, but if the value in E is not one of the above and the value in I does not end in "-0" delete entire row.

The spec

 

[rkol297]

Example:

Column E: Column I:
GGIS 659568-0 <-keep (E isnt on list but I ends in -0)
GIMN 659586-5 <- delete row (not on list in column E and "I" doesnt end in -0
FRTS 6548905-0 <- keep (E isnt on list but I ends in -0)
ZEMA 5894512-0 <-Keep (zema is on the list)
ZEMA 5895625-9 <-keep (zema is on the list)

 

[Norie]

This is what the code I posted gives using that data.

Is the result you would want from that data?

[TABLE="width: 293"]
<tbody>[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]GGIS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]659568-0[/TD]
[/TR]
[TR]
[TD]FRTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6548905-0[/TD]
[/TR]
[TR]
[TD]ZEMA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5894512-0[/TD]
[/TR]
[TR]
[TD]ZEMA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5895625-9[/TD]
[/TR]
</tbody><colgroup><col span="4"><col></colgroup>[/TABLE]

 

[rkol297]

Based on the two codes that were posted I deduced this from them did I modify the code correctly or do you see an error?

With the code below

column E column I
GIGS 658953-9 is not being deleted for some reason


Sheets("IG MEDCO").Select
Dim CelIGMEDCO0F As Range, RngIGMEDCO0F As Range, iIGMEDCO0F As Long
Set RngIGMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iIGMEDCO0F = RngIGMEDCO0F.Count To 1 Step -1
If RngIGMEDCO0F(iIGMEDCO0F).Value Like "*-0*" Then
Select Case Range("E" & iIGMEDCO0F).Value
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
'do nothing
Case Else
'delete row
If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" Then
Rows(iIGMEDCO0F).Delete
End If
End Select
End If
Next iIGMEDCO0F

 

[rkol297]

I GOT IT TO WORK!!!

Sheets("IG MEDCO").Select
Dim CelIGMEDCO0F As Range, RngIGMEDCO0F As Range, iIGMEDCO0F As Long
Set RngIGMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iIGMEDCO0F = RngIGMEDCO0F.Count To 2 Step -1
Select Case Range("E" & iIGMEDCO0F).Value
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
'do nothing
Case Else
If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" _
Then Rows(iIGMEDCO0F).Delete
End Select
Next iIGMEDCO0F

THANKS FOR THE HELP MAN!

 

[Norie]

Glad it's working, though I don't see much difference in the code of the last 2 posts.

Anyway, that doesn't matter.