If string detected in a cell, clear entire row

[Nelson78]

Hello everybody.

I'm struggling with this job: for each cell in a column, I have to check if the letter "I" is contained.
If yes, clear the entire row.

Now, with the following code something is wrong (all rows are cleared, also if "I" is not contained).

If InStr(1, cell, “I”) > 0.5 Then cell.EntireRow.Clear

Thank's in advance.

 

[JonXL]

Hello everybody.

I'm struggling with this job: for each cell in a column, I have to check if the letter "I" is contained.
If yes, clear the entire row.

Now, with the following code something is wrong (all rows are cleared, also if "I" is not contained).

If InStr(1, cell, “I”) > 0.5 Then cell.EntireRow.Clear

Thank's in advance.

Try using .Value for the InStr,

InStr(1, cell.Value, “I”)

 

[Nelson78]

No.

Same (wrong) result.

 

[JonXL]

No.

Same (wrong) result.

What does the rest of your code look like?

 

[Fluff]

If your cells contain values, rather than formulas, how about

Sub dellIRows()
   With Range("[COLOR=#ff0000]J:J[/COLOR]")
      .Replace "*I*", "=XXX", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
End Sub

Change value in red to suit

 

[Nelson78]

@Fluff and @JonXL thank you, both the solution are ok (Jon, at first I had a problem with my file and because of that it has not worked until I fixed it).

 

[Fluff]

Glad we could help & thanks for the feedback