Deleting Rows Based on Column Cell Content

[dantb]

Hello all:, I have found a lot of vb on deleting rows if it contains a certain value or word, but I can not find out how to delete a row if it does not have a certain value. My workbook in column ( C ) contains the word ( PROD: ) and I am looking for a way that if that word is not found in Column C, Delete the Entire row. My Data ends on row 800 also. Thanks Dan

 

[Banker1]

An example of a VBA IF statement that would fit is:

If Cell.Value <> "PROD:" Then
Cell.EntireRow.Delete
End IF

 

[Banker1]

Here is an example I tested with some VBA:

Book1
	A	B	C	D
1	1		Prod:
2	2
3	3		Prod:
4	4
5	5		Prod:
6	6		Prod:
7	7		Prod:
8	8
9	9		Prod:
10	10		Prod:
Sheet1

Disregard the cells with &nsbp, not sure why that shows up.

Here is the code:<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()<SPAN style="color:#00007F">Dim</SPAN> WorkRange<SPAN style="color:#00007F">As</SPAN> Range<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("C1:C" & Range("C65536").End(xlUp).Row)<SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> Cell<SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> UCase(Cell.Value)<> "PROD:"<SPAN style="color:#00007F">Then</SPAN>
        Cell.EntireRow.Delete
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> Cell<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[dantb]

This is what I have been trying but it will Lock Excel up, Do not run code. I just dont know what is wrong with the statement. Thanks Dan


LastRow = Range("C65536").End(xlUp).Row
For i = 1 To LastRow
If Range("C" & i) = "PROD:" Then
Rows(i).Delete shift = xlShiftUp
i = i - 1
End If
Next i

 

[dantb]

Thanks Banker that is what I was looking for, been driving me nuts. Thanks again Dan