Help with Case Code

[rkol297]

I have a spreadsheet that in Column E there are therapy type abreviations and in Column I there are numbers that end in - # (example 5965859-0 or 5965895-8 or 598654-5) I need to have code that first looks at the abreviations in Column E and on one tab named "IG MEDCO SETUPS" I need to keep all rows that contain any of the following "ARAL", "BERT", "GLSA", "ZEMA" and on another tab called "PAH MEDCO SETUPS" I need to keep all values that contain these abreviations in Column E </SPAN>"EPAC", "EPAP", "EPOP", "FL50", "F100", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT" now for all other abreviations in column E that are not outlined above I want to keep only those rows that end in "-0" in Column I.

Here is the case code that I have attempted to do this with but for some reason it is not doing what I want:

'###REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES ON IG MEDCO TAB###</SPAN>
    Sheets("IG MEDCO SETUPS").Select</SPAN>
    Dim CelIGMEDCO0F As Range, RngIGMEDCO0F As Range, iIGMEDCO0F As Long</SPAN>
    Set RngIGMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)</SPAN>
    For iIGMEDCO0F = RngIGMEDCO0F.Count To 2 Step -1</SPAN>
        Select Case UCase(Trim(Range("E" & iIGMEDCO0F).Value))</SPAN>
            Case "ARAL", "BERT", "GLSA", "ZEMA"</SPAN>
                'do nothing</SPAN>
            Case Else</SPAN>
                If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" _</SPAN>
                Then Rows(iIGMEDCO0F).Delete</SPAN>
            End Select</SPAN>
    Next iIGMEDCO0F</SPAN>
 
'###REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES FOR PAH MEDCO TAB###</SPAN>
    Sheets("PAH MEDCO SETUPS").Select</SPAN>
    Dim CelPAHMEDCO0F As Range, RngPAHMEDCO0F As Range, iPAHMEDCO0F As Long</SPAN>
    Set RngPAHMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)</SPAN>
    For iPAHMEDCO0F = RngPAHMEDCO0F.Count To 2 Step -1</SPAN>
        Select Case UCase(Trim(Range("E" & iPAHMEDCO0F).Value))</SPAN>
            Case "EPAC", "EPAP", "EPOP", "FL50", "F100", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT"</SPAN>
                'do nothing</SPAN>
            Case Else</SPAN>
                If Right(Range("I" & iPAHMEDCO0F).Value, 2) <> "-0" _</SPAN>
                Then Rows(iPAHMEDCO0F).Delete</SPAN>
            End Select</SPAN>
    Next iPAHMEDCO0F</SPAN>

 

[par60056]

My first guess is that the ranges you are getting are not what you think they are.

In the debugger, see what the value of "RngIGMEDCO0F.count" is.

There are a number of ways that you can find the last row in the sheet. Check out some of the other posts (People tell me the way I do it is not reliable so I won't post it for you)