Search Column for Largest Value, Set Focus to That Cell

J

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
Hi, everyone, I'm using Excel 2016. I just have a quick and (hopefully) simple question.

I'd like Excel to search a particular column for the largest value in that column. I would then like Excel to set the focus to the entire row that this number is in.

Or, at the very least, set the focus to the cell with the highest number. (It isn't really absolutely necessary or essential to highlight the whole row. The cell will suffice.)

I'm attaching a sample spreadsheet. In Column G, the largest value is 9.67, which is cell G7. The focus should be set on G7, if the entire row can't be focused. Whichever is easiest is what I prefer.

I know how to use the MAX formula, which finds the largest number in a selected series of cells. I've created a cell that has the results of that MAX formula. But performing a routine that focuses on the one cell…I haven't figured that out yet.

Would anyone be willing to steer me in the direction of how to do this? Thank you.
Jack Danniel
 

Attachments

  • Blades.xlsx
    10.6 KB · Views: 12
Code: 
Option Explicit


Sub MaxNr()
    Dim i As Long, lr As Long, rownr As Long
    lr = Range("G" & Rows.Count).End(xlUp).Row
    Range("I2") = WorksheetFunction.Max(Range("G2:G" & lr))
    For i = 2 To lr
        If Range("G" & i) = Range("I2") Then
            Range("G" & i).EntireRow.Select
            Exit Sub
        End If
    Next i


End Sub
 
Another option
Code: 
Sub SelectMaxValue()
   Dim Var As Variant
   Var = Evaluate("match(max(G:G),G:G,0)")
   If Not IsError(Var) Then Range("G" & Var).Select
End Sub
 
I am so sorry for not following up to say thank you. I apologize, been on vacation and was not working on spreadsheets. Thank you to you both, for replying and being kind enough to post code.
 
Glad we could help & thanks for the feedback
 
You must log in or register to reply here.
Back
Top