Selecting The Maximum Value In A Range

Category: General VBA | [Item URL]

This tip describes a simple utility that activates the worksheet cell that contains the maximum value. The VBA routine determines the maximum value in the selected range. If a single cell is selected, it determines the maximum value for the entire worksheet. Next, it use the Find method to locate the value and select the cell.

To use this subroutine:

1. Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
2. If you want to search for the maximum value in a specific range, select the range. Otherwise, select any single cell.
3. Execute the GotoMax subroutine.

The GoToMax Subroutine

Sub GoToMax()
'   Activates the cell with the largest value
    Dim WorkRange as Range

'   Exit if a range is not selected
    If TypeName(Selection) <> "Range" Then Exit Sub

'   If one cell is selected, search entire worksheet;
'   Otherwise, search the selected range
    If Selection.Count = 1 Then
        Set Workrange = Cells
    Else
        Set Workrange = Selection
    End If 

'   Determine the maximum value
    MaxVal = Application.Max(Workrange)
    
'   Find it and select it
    On Error Resume Next
    Workrange.Find(What:=MaxVal, _
        After:=Workrange.Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False _
        ).Select
    If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal
End Sub