VBA - Delete Rows based on cell value

[Jason Micahel]

I have wrote code to delete a row if the value equals something. Sample Code Below:
lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 16).Value = "OP" Then Rows(z).Delete
Next z

So in this code I am telling it to go up one row at a time, look at column 16 to see if "OP" is in the column if it is then delete the row. and repeat the next row.
Can someone please help me out with this, I am trying to write something to delete rows if the cell value is not equal to something.. And if it is possible I want to say If Cells(z, 16).Value <1 Then Rows(z).Delete
but this give me an error..

So basically I need your help with a code for not equal to a cell value then delete rows, and if cell value if less than "X" then delete rows..

Thanks in advance for your time and thoughts on this....

 

[Aaron Blood]

I haven't posted this trick here before. It's an interesting Find function for use in VBA apps. Helps greatly with specific search & destroy missions.
http://www.xl-logic.com/xl_files/vba/kickbutt_find_function.zip


As for a simpler macro to S&D on values less than x, look at this example.
http://www.xl-logic.com/xl_files/vba/delete_if.zip

Just change the logic for the line:
If cell.Value = x Then

To be:
If cell.Value < x Then

 

[Jason Micahel]

Thanks that help out some..
Do you know how to write not equal to in VBA. ???

 

[martinee]

Not equal to is: <>

For example:
"y not equal to x" becomes:

y <> x

HTH.

 

[Jason Micahel]

That makes sense, thanks..
Stupid question time for you..

to put this in my code to say is a cell is not equal to X then ???

lRow = Cells(65536, 1).End(xlUp).Row
For z = lRow To 1 Step -1
If Cells(z, 16).Value <> 1 Then Rows(z).Delete
Next z

So this would be what I need if I am trying to delete anything in column N(16) that is not equal to 1.

 

[martinee]

That appears good to me. Does it not work?

 

[Jason Micahel]

No I am getting Sytax error..
Any ideas

 

[yussi1870]

You need the following line of code:
End If