Excel VBA deleting rows that do NOT have specific value (text) - causes Error Code 13

Windows 10 Excel 2019

I have the below VB code that is throwing a Run-Time code 13 mismatch error and I can not work out why.

It appears that the line

Please Login or Register  to view this content.

is causing the issue.

I have a Excel 2019 Worksheet, "Track Data", with data in Columns A - I
This worksheet has headers in row 1.

The data in Columns A-I is pulled by formulas using VBA from a different worksheet in the same workbook.

For Column I, if certain conditions are met on the other sheet the formula for Column I will either the
text, S, M or S+M, or it will return nothing. It is not possible for any other text to be entered by the formula.

The code below 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 then only leave rows where Column I either has "S", "M" or "M+S".

I have also tried placing double "" like.. ""S"", ""M"" or ""M+S"" but I still get the Run-Time code 13 mismatch error


I would have thought it should work as it is only looking for those three specific text values.

Please Login or Register  to view this content.

To debug the issue, add this line to the code:

Please Login or Register  to view this content.

Run/step through the code and when it errors look in the immediate window (CTRL+G to show it if it is not already visible) to see which cell the error occurred on.
Then go and look at the value in that cell - it very likely contains an error value such as #VALUE! or #REF!.

Originally Posted by Belinea2010

Hi Colin

Thank you for your reply.

As you suggested in your post I added "Debug.Print .Address(External:=True); ":"; vbTab; .Value" to my code and ran it.

Excel highlighted .Address in your code and gave the error "Complie error: Invalid or Unqualified reference.

thanks

Hi, it needs to go inside the With block as shown in my post.

Try this instead...
If not, please upload a sample file...

Please Login or Register  to view this content.

Can also be achieved without a loop...