instr deleting

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
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...

Code:
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Nov 25, 2009
Messages
164
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top