Find the highest value in column A and delete the whole row

[VBAProIWish]

Hello all,

I need code that will find the highest value in column A and delete that whole row..

Thanks!

 

[jbeaucaire]

Try this:

Option Explicit

Sub DeleteMaxRow()
'Jerry Beaucaire   6/3/2010
'Find highest value in column A and delete that row

Dim delRw   As Long
Dim delVal  As Double

    delVal = Application.WorksheetFunction.Max(Range("A:A"))
    delRw = Application.WorksheetFunction.Match(delVal, Range("A:A"), 0)
    
    Rows(delRw).Delete xlShiftUp

End Sub

 

[VBAProIWish]

Just what I needed!

Thanks

 

[Domenic]

What if there are two or more rows with the largest value?

 

[jbeaucaire]

If you need to delete ALL the rows that have the highest value, in case of duplicates, use this instead.

Option Explicit

Sub DeleteMaxRow()
'Jerry Beaucaire   6/3/2010
'Find highest value in column A and delete that row

Dim delRw   As Long
Dim delVal  As Double

    delVal = Application.WorksheetFunction.Max(Range("A:A"))
    delRw = Application.WorksheetFunction.Match(delVal, Range("A:A"), 0)
    
On Error Resume Next
    Do
        Rows(delRw).Delete xlShiftUp
        delRw = 0
        delRw = Application.WorksheetFunction.Match(delVal, Range("A:A"), 0)
    Loop Until delRw = 0

End Sub

 

[VBAProIWish]

Domenic, JB...

Once I got my answer, I didn't see the responses after my reply. This macro was basically needed to delete the auto-sum row, so there would never be a duplicate highest value, but good forward thinking and resolving on both of your parts nonetheless!

Thanks again