instr deleting

[detlion1643]

When trying to loop through a column and delete a row based on the cell containing exctly 0 (not sure if it's formatted number or text) it will delete anything with a 0 in it ex) 3402, 403...

With Worksheets("Sheet1")
        Set Rng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
        End With
        For i = Rng.Rows.Count To 1 Step -1
        If InStr(1, Rng.Cells(i, 1).Value, "0") Then
        Rng.Cells(i, 1).Select
            Rng.Cells(i, 1).EntireRow.Delete shift:=xlUp
        End If
        Next i
End Sub

I thought instr only found exaclty what's in "", but it's acting similar to Like
I tried using If InStr(1, Rng.Cells(i, 1).Text, "0") Then
but same thing.

 

[ChrisM]

You're on the right track, but you missed one key point, you need to consider what value Instr is going to return.

The help file is great for this, the first thing it says is 'returns a Long...'

That means Instr is returning a number. Now, what number is it returning? It will return a zero if the character is not found anywhere in the string, else it returns the location of the character in the string, so '1023' would return '2'.

Your If-Then isn't really doing anything. For example, it's just saying "If 2 Then do this".

If you only want cells equal to exactly zero, why not just:

If Rng.Cells(i, 1).Value = 0 Then

 

[detlion1643]

Thanks Chris... I am not sure why I was hung up with instr(), maybe i've been seeing too much of it lately with my project, I forget the simple things.

Anyways, thanks!

 

[kab7030]

why not just do comparison using =