Delete Rows That Do Not Meet Criteria

[Aschr13]

Hello,

I am working in a 3,300 row excel sheet and I would like to delete the rows that I don't need. I want to delete rows that do not contain a certain NAICS classification, do not occur in a certain states, and will be awarded via "sole source" (I work in contracting).

I have tried several codes that I have found on other Mr. Excel posts and elsewhere and so far the best I've managed to do is delete the rows that contain the criteria I want to see instead of the rows do not contain the criteria. I have also run the macro and every row disappears and run the macro and seemingly random rows disappeared that were outside the criteria listed.

Here is an example of a code that worked:

Sub DeleteRowsNot_Contain()
     Dim ws As Worksheet
     Dim lastRow As Long
     Set ws = ActiveWorkbook.Sheets("Evaluating")
     lastRow = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
     For i = lastRow To 1 Step -1
       If InStr(1, Cells(i, "G").Value, "541715  |   Research and Development in the Physical, Engineering, and Life Sciences (except Nanotechnology and Biotechnology", vbTextCompare) _
       Or InStr(1, cell, "512110 | Motion Picture and Video Production", 1) _
       Or InStr(1, cell, "541330  |   Engineering Services", 1) _
       Or InStr(1, cell, "562910  |   Remediation Services", 1) _
       Or InStr(1, cell, "541370  |   Surveying and Mapping (except Geophysical) Services", 1) _
       Or InStr(1, cell, "541618  |   Other Management Consulting Services", 1) _
       Or InStr(1, cell, "541620  |   Environmental Consulting Services", 1) _
       Or InStr(1, cell, "541690  |   Other Scientific and Technical Consulting Services", 1) _
       Or InStr(1, cell, "541990  |   All Other Professional, Scientific, and Technical Services", 1) _
       Or InStr(1, cell, "561110  |   Office Administrative Services", 1) _
       Or InStr(1, cell, "561320  |   Temporary Help Services", 1) _
       Or InStr(1, cell, "561990  |   All Other Support Services", 1) _
       Or InStr(1, cell, "562910  |   Remediation Services", 1) _
       Or InStr(1, cell, "611430  |   Professional and Management Development Training", 1) _
       Or InStr(1, cell, "611699  |   All Other Miscellaneous Schools and Instruction", 1) _
       Or InStr(1, cell, "611710 | Educational Support Services", 1) = 0 Then
         Rows(i).EntireRow.Delete
       End If
     Next
 End Sub

This code deleted all of the values that contained these values (the rows I want to see), instead of all the rows that did not contain these values (the rows I do not want to see). I have learned that EntireRow.Delete is why this happened. I have considered trying to do the inverse of this, rather than listing all the things I want to see, list the things I don't want to see. The problem with that is there are hundreds of values that could pop up on this excel report. It isn't feasible to code for all of the things that I don't want to see.

I'd also like to remove rows that have a Place of Performance that is not Washington, Oregon, and California and all rows that are not "competed."

I'm sure I'm leaving out important information, so please call me out and let me know what you need.

 

[Peter_SSs]

Thanks for the answers to my questions.
It does sound like it will be tricky to get working code as you are saying that the data can vary quite a bit.

Please review post #3 about how to post vba code in the forum. Compare reading the code in post #10 to the codes in Post #s 9, 7 (& 1 where I fixed the tags for you).

It would be good to have a smallish set of sample data and expected results to test with otherwise there is a lot of typing (& likely mistakes) to have something to test with. Please review post #6 regarding XL2BB problems.

 

[bebo021999]

I believe this is not a very difficult issue. If you can share a sample via a free sharing site, like gg drive, dropbox....