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 


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy