Finding highest number in a range of cells

Hi,

I am working with a "register" in excel. What VBA code should I use to find the highest number in a range of cells (not sorted in particular order)? All numbers are integers.

I would like to know the actual number in that cell, the cell reference and the row number.


Thx,


Marcus

Thx Roy,

this helped perfectly fine.

Marcus

Originally Posted by royUK

Like this

Option Explicit

Sub findMax()

    Dim rng    As Range
    Dim cl     As Range
    Dim maxNum As Long

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

    maxNum = Application.WorksheetFunction.Max(rng)

    Set cl = rng.Find(maxNum, LookIn:=xlValues)

    If Not cl Is Nothing Then

        MsgBox " Maximum value: " & maxNum & vbNewLine & "Located in:" & cl.Address & _
               vbNewLine & "In Row: " & cl.Row
    End If

End Sub