getting the number of the row with the maximum value

[Guest]

i have a a column with values, by using a macro i want to get the number of
the row in which the maximum value of the column is.

 

[Guest]

Are you sure you want to do it by macro?

First you'd use the MAX() function to find the largest value. Now, if you
want to do this by macro (presumably to SELECT the cell containing the
largest value), you'd use the Find command.

I'm assuming your values are in a range named 'rngValues'

Dim r As Range
Set r = Range("rngValues")
r.Find(WorksheetFunction.Max(r)).Select

If you don't need a macro, then you'd use the MATCH() function to find the
relative position of that MAX value in the list.

=MATCH(MAX(rngValues),rngValues)

If your data starts in any row but the first, you'll have to convert the
relative position to a row #. Do this by adding a value to the MATCH() equal
to the number of rows down the sheet your range starts. Thus, if your data
starts in row 2, then the data starts one row from the top and the formula
needs to be

=MATCH(MAX(rngValues),rngValues)+1

Duke

 

[P Sitaram]

=ROW(INDEX(A1:A100,MATCH(MAX(A1:A100),A1:A100,0)))

 

[Bernie Deitrick]

hilbert,

Dim myRng As Range
Set myRng = Range("A:A")
With Application.WorksheetFunction
MsgBox .Match(.Max(myRng), myRng, False)
End With

or with a formula:

=MATCH(MAX(A:A),A:A,FALSE)

HTH,
Bernie
MS Excel MVP