Selecting the largest valued cell using VBA

[ifu06416]

Hi there

I've looked around for solution to this but surprisingly couldn't find one.

I would like to build a macro that selects the largest valued cell in column A.

it should be easy enough but i guess im not describing it right in my google searches.

Regards,

John.

 

[Dan Waterloo]

Hi John. Are you trying to find the largest value in the column or the cell that has the largest value? If the former, I recommend reading up on the LARGE function. If the latter, an important question would be whether your data is sorted by that column or not and how many rows you are typically dealing with.

 

[PatOBrien198]

Try this....

Sub test()

Dim rng As Range
Set rng = ActiveSheet.Range("A:A").Find(LookIn:=xlValues, What:=Application.WorksheetFunction.Max(ActiveSheet.Range("A:A")))
If Not rng Is Nothing Then
    rng.Select
End If
End Sub

 

[ifu06416]

Perfect, thanks Pat

 

[Dan Waterloo]

You're welcome! No problem!

 

[PatOBrien198]

LOL. Thanks Dan for the early morning humor. It got my day started out right!