Delete rows that doe not match

[mecerrato]

I have this code that I modified from another user on this forum and retrofitted for my need. However, although it works perfectly the first time, every time I re-run it it keeps deleting one additional row when it should not delete any as the first time it was run it removed all rows that matched my criteria.

Can someone help me tweak this code?

Sub DelAllButAlvaro()

For j = 1 To 1
With Sheets("Pipeline")
    .AutoFilterMode = False
    .Range("$a$11:$cq$500").AutoFilter Field:=2, Criteria1:="<>" & "Alvaro Molinares"
    .Range("B11:B" & .Cells(Rows.Count, "B").End(xlUp).Row).EntireRow.Delete
End With

Next j
Call Show_Realdeals
End Sub

 

[Joe4]

A few things.

This line serves no purpose in your code:

For j = 1 To 1

That is telling your loop to run exactly once, which is what would happen without the loop!

The issue is if you have no matching entries, you are still telling it to delete row 11!
So you need to check/confirm you have entries to delete.
I am not sure if you have headers in your data, and what row they are on (10 or 11), but you need to do something like this:

Sub DelAllButAlvaro()

Dim lr As Long

With Sheets("Pipeline")
    .AutoFilterMode = False
    .Range("$a$11:$cq$500").AutoFilter Field:=2, Criteria1:="<>" & "Alvaro Molinares"
    lr = .Cells(.Rows.Count, "B").End(xlUp).Row
    If (lr > 11) Or (Range("B11") <> "Alvaro Molinares") Then .Range("B11:B" & lr).EntireRow.Delete
End With

Call Show_Realdeals

End Sub

The code may have to change slightly depending on if and where you have headers.