IF with AND condition does not read particular string value but reads other strings

[vbintern]

I have a dataset where each rows' first column value is some string. Now there can be rows with different types of strings on the first column cell. However, I only want to keep records with 5 specific string names in the first cell of the row and delete the rows with any other string names. I have written down the following code which works fine while testing 3 string names. But this code fails to check for the other 2 specific string names. Additionally, rows with these 2 specific string names (in the first cell) have 2 cell fields in the row that are identical values (for rows with other 3 string names, all cell values are different) (IS HAVING IDENTICAL VALUES in cells an issue for not getting retained?)

NOTE:
-> this code is a part of a sub
-> actual data is confidential hence adding sample string names that are similar to actual
-> code works well for ABC_DEF_LMN, ABC_DEFSBA and ABC_DEFDBMS
-> code fails to read ABC_CHCDO and ABC_CDONA in first cell of each row
-> the identical values present in 2 cells in rows with those failed string names are a mix of letters, numbers and 2 colons (CAN THIS BE AN ISSUE THAT IT IS FAILING?)

CODE:

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If ((Cells(i, "A").Value) <> "ABC_DEF_LMN" And (Cells(i, "A").Value) <> "ABC_DEFSBA" And (Cells(i, "A").Value) <> "ABC_DEFDBMS" And (Cells(i, "A").Value) <> "ABC_CHCDO" And (Cells(i, "A").Value) <> "ABC_CDONA") Then
Cells(i, "A").EntireRow.Delete
End If
Next i

Only rows with above mentioned 3 string names are retained. Rows with other 2 names are deleted (which I want to retain too).
Please help me resolve this. I don't know why Excel is deleting those records when the syntax is correct.

 

[DanteAmor]

The code is correct.
Check that the cell does not have blank spaces before or after the text.
The code is case sensitive, in the code (in your example) you have uppercase, maybe in the cell you have lowercase.

For multiple conditions, it is easier to use the Case statement:

Sub test1()
  Dim last As Long, i As Long
  last = Cells(Rows.Count, "A").End(xlUp).Row
  For i = last To 2 Step -1
    Select Case UCase(Cells(i, "A").Value) 'Convert to uppercase to compare only uppercase
      Case "ABC_DEF_LMN", "ABC_DEFSBA", "ABC_DEFDBMS", "ABC_CHCDO", "ABC_CDONA"
      Case Else
        Cells(i, "A").EntireRow.Delete
    End Select
  Next i
End Sub

 

[vbintern]

The code is correct.
Check that the cell does not have blank spaces before or after the text.
The code is case sensitive, in the code (in your example) you have uppercase, maybe in the cell you have lowercase.

For multiple conditions, it is easier to use the Case statement:

Sub test1()
  Dim last As Long, i As Long
  last = Cells(Rows.Count, "A").End(xlUp).Row
  For i = last To 2 Step -1
    Select Case UCase(Cells(i, "A").Value) 'Convert to uppercase to compare only uppercase
      Case "ABC_DEF_LMN", "ABC_DEFSBA", "ABC_DEFDBMS", "ABC_CHCDO", "ABC_CDONA"
      Case Else
        Cells(i, "A").EntireRow.Delete
    End Select
  Next i
End Sub

Hey, thanks for responding.
To note, all the first column cell values are by default in upper case and there are no blank spaces (when I manually checked in excel) before or after the strings.
I tried your code but it still fails to retain rows with those 2 strings. Is the 2 identical cell values as mentioned in my first message a reason for this? Please help me figure out the problem.

 

[vbintern]

Hi DanteAmor, I figured out why it was getting deleted. There was another case sensitive criteria also in other cell of each row to which I was applying similar logic of retention/deletion which is why it was accidentally deleting these records.

Nevertheless, thanks for responding and providing me an elegant way of retaining records using the Case approach. Learned something new!

 

[DanteAmor]

I'm glad to hear that.