Not deleting rows that match criteria

[fjdurbin]

After an extensive search of the web I did not find a satisfying way to delete rows that do not match my criteria. What I ended up doing can be seen below. My solution was to change the color of cells that match the criteria then delete the rows that do not have the cell color. It works but there has to be a better way. Any suggestions?...JD

Sub ColorRws()
Dim z As Long
Dim x As Long
z = GetLastRowWithData
For x = z To 2 Step -1
If Left(Range("a" & x).Value, 3) = "123" Or Left(Range("a" & x).Value, 3) = "321" Then 'Rows(x & ":" & x).EntireRow.Delete
Range("a" & x).Interior.ColorIndex = 4
End If
Next x

z = GetLastRowWithData
For x = z To 2 Step -1
If Range("a" & x).Interior.ColorIndex <> 4 Then
Rows(x & ":" & x).EntireRow.Delete
End If
Next x
End Sub

 

[HRIS]

Not sure if this covers what you're wanting to do, but for something I was working on I used the autofilter in my macro and filtered for rows not equaling FTR. Then selected the rows showing and deleted.

There is probably some cleanup that could be done to improve this, but the code is below...

FYI - the last line of the code is just to reset the autofilter so the list shows all upon completion of the macro.

        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=17, Criteria1:="<>FTR", Operator:=xlAnd
        Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Delete Shift:=xlUp
        Selection.AutoFilter Field:=17