Selecting The Maximum Value In A Range
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:
- Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
- If you want to search for the maximum value in a specific range, select the range. Otherwise, select any single cell.
- 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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
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