Deleting rows that do not have specific value (text) - throwing Error Code 13

Question

The below VBA code is throwing

Run-Time code 13 mismatch error

I have a Windows 10, Excel 2019 Worksheet with data in Columns A - I.

This code looks at Column I and where it finds a cell that does not have the text "S" or "M" or "S+M" it is supposed to delete the whole row.

This would leave rows where Column I has "S", "M" or "M+S".

I tried placing double "" like ""S"", ""M"" or ""M+S"".

I still get

Run-Time code 13 mismatch error

Sub DeleteRowsTEST()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long

' Defines LastRow as the last row of data based on column I
LastRow = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row

' Sets check range as I1 to the last row of I
Set cRange = Range("I1:I" & LastRow)

' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
        If .Value <> "M" And .Value <> "S" And .Value <> "M+S" Then
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x

End Sub

Answer 1

I think the InStr() function would be the most valuable. This function checks a string for something. In your case it would be for either "S" or "M" If it has any combination of these values it will return a value. By setting it up to > 0 means that if a string has an "S" or "M" it will return a value greater than 0. The code then should delete the row. If it doesn't find either then it returns 0 and continues.

You could set up a function in your with code similar to this:

---

dim smCode As String 'declare at the beginning of your code
 
With cRange.Cells(x)
smCode = cstr(.value) 

'not sure if the cstr function is needed or will work inside a with block, I would try with the cstr and with out it and see the results.

 ' If the cell does not contain one of the listed values then...
        If InStr(smCode, "S") > 0 or If InStr(smCode, "M") > 0   Then
            ' Delete that row
            .EntireRow.Delete
        End If
End With