VBA: How to search for value in column, sort through other column and delete row

[egraham3]

Hello,

I need help writing code that will search for a name, which a column may have multiple, then look to another column and delete each row that has a lower value than the high value.

so for example, search for john

A B

1 John .5
2 John .7
3 John .1
4 Dave .4
5 Dave .5
6 Jess .2

i only want it to keep the 2nd row of the john. Delete the 1st and 3rd row because column B has lower values than the 2nd.

Then i want the macro to be able to search for dave, and delete column 4 and so on.

I hope this clarifies what im looking for. I just dont quite understand how to put together a macro that will search criteria, and sort through it in another column.

Any help would be greatly appreciated

 

[John Davis]

Hello,

I need help writing code that will search for a name, which a column may have multiple, then look to another column and delete each row that has a lower value than the high value.

so for example, search for john

A B

1 John .5
2 John .7
3 John .1
4 Dave .4
5 Dave .5
6 Jess .2

i only want it to keep the 2nd row of the john. Delete the 1st and 3rd row because column B has lower values than the 2nd.

Then i want the macro to be able to search for dave, and delete column 4 and so on.

I hope this clarifies what im looking for. I just dont quite understand how to put together a macro that will search criteria, and sort through it in another column.

Any help would be greatly appreciated

If it's OK to sort, this might help:

Sub egraham3()
Dim lr As Long
Dim i As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("A2:B" & lr)

    .Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

    For i = lr To 2 Step -1
    
        If Range("A" & i) = Range("A" & i).Offset(-1) Then
    
            If Range("B" & i).Value < Range("B" & i).Offset(-1).Value Then
        
                Range("B" & i).EntireRow.Delete shift:=xlUp
            
             End If
             
        End If
        
    Next i
    
    
End With


End Sub