Home |
Search |
Today's Posts |
#1
|
|||
|
|||
getting the number of the row with the maximum value
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. |
#2
|
|||
|
|||
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 "hilbert" wrote: 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. |
#3
|
|||
|
|||
=ROW(INDEX(A1:A100,MATCH(MAX(A1:A100),A1:A100,0)))
|
#4
|
|||
|
|||
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 "hilbert" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum Number of Cell Formats | Excel Discussion (Misc queries) | |||
maximum number of spreadsheets in one workbook | Excel Discussion (Misc queries) | |||
Is there a maximum number of non-contiguous columns that can be h. | Excel Discussion (Misc queries) | |||
how to increase maximum number of columns in excel 2003 | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |