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
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