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

 

[gaj104]

If I've understood, try this:

Function xxx()

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
        Select Case Range("E" & RngMEDCO0F(iMEDCO0F).Row).Value
            Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
                'do nothing
            Else
                'delete row
                RngMEDCO0F(iMEDCO0F).EntireRow.Delete
        End Select
    End If
Next iMEDCO0F

End Function

 

[Norie]

That code looks like it's deleting rows with "-0" at the end.

Anyway, if you do want to keep rows with "-0" at the end in column I or one of the terms you listed in column E.

For I = RngMEDCO0F.Count To 1 Step -1
  Select Case Range("E"& I).Value 
        Case "ARAL", "BERT", "EPAC"     ' add rest with quotes around them EPAP, EPOP, FL50, F100, GLSA, REMO, REMP, RMIP, RMIV, VENP, VENT, ZEMA, ZYME, ZYMF
            ' Do nothing
        Case Else
             If Right(Range("I" & I).Value, 2) <>"-0" Then 
                Rows(I).Delete
             End If
   End Select
Next I

 

[rkol297]

No go it actually is keeping the rows for other values in column E, and not deleting them.

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" & RngIGMEDCO0F(iIGMEDCO0F).Row).Value
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
Case Else
If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" Then
Rows(iIGMEDCO0F).Delete
End If
End Select
End If
Next iIGMEDCO0F

Maybe some clarification on my part would help:
Scenario 1:
IF Column E contains any of these:

"ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"

Keep the row no matter what value is in I


Scenario 2:
IF Column E is NOT one of these:

"ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"

Then delete entire row if value in "I" DOES NOT END WITH "-0"


The help is greatly appreciated!

 

[Norie]

My code does that.

 

[rkol297]

MY FAULT THIS CODE DOES WORK ON THE SHEET I WAS ASKING FOR, THANK YOU.

I HAVE A SECOND ISSUE HOWEVER.

I have another sheet that instead of deleting the rows when it contains the list of specific values in column "E" I want to keep the row regardless of what is in column "I" and also want to keep the row if Column "I" ends with "-0" regardless of what is in column "E"

basically delete any thing that is not in the list of specific ranges in column "E" unless it has a value ending in "-0" in column "I"

make sense?

 

[Norie]

Sorry 'instead of deleting rows when it contains list of specific values'?

Both codes posted do not delete rows which have a value from the list in column E.

I think you need to post some sample data to clarify what you have and what you want.

 

[rkol297]

The code posted is keeping the values that I have listed but it isn't deleting the rows that have other values in column E that do not have a value ending in "-0" in column I

does that make sense?

 

[Norie]

If the code is keeping the rows you want what are you referring to here?

deleting the rows when it contains the list of specific values in column "E"

Is this what you want the new code to do?

 

[rkol297]

Ignore that post I was thinking backwards


I need the code to delete row if:
Column "E" is NOT one of the specific values listed AND if column "I" DOES NOT end with "-0"

right now its leaving other values not in the list in column E that have values that do not end in -0