Exclude the only one value and delete all values in vba excel

[mayaa_mmm]

When i apply filter , it should uncheck only John and apply filter to all the values except john and delete the rows till last value

Mark	Name
80	John
20	Maya
30	John
40	Peter

In my report , John is the standard person. But Names daily will get change. I need to delete the rows except John.

 

[JLGWhiz]

You can try this to see if it is what you want.

Sub t()
With ActiveSheet.UsedRange
    .AutoFilter 2, "<>John"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[mayaa_mmm]

You can try this to see if it is what you want.

Sub t()
With ActiveSheet.UsedRange
    .AutoFilter 2, "<>John"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

It works but delete all the rows ,keep the header alone

I have written the logic like this
ActiveSheet.Range("M:M").AutoFilter Field:=13, Criteria1:="<>John"
but Error is thrown in the logic

 

[mayaa_mmm]

It works but delete all the rows ,keep the header alone

I have written the logic like this
ActiveSheet.Range("M:M").AutoFilter Field:=13, Criteria1:="<>John"
but Error is thrown in the logic

Sorry for the trouble.
Your logic is working for me, I fixed it.

Thank you

 

[JLGWhiz]

You're welcome,
regards, JLG