Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return row #
Hello,
Is there an efficient way to return the row # corresponding to the row that contains the maximum value in a column range? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return row #
Hi Bing,
In VBA you can do it like this - Sub GetMaxRow() MsgBox MaxRow(Range("A1").EntireColumn) End Sub Function MaxRow(colrng As Range) MaxRow = Application.Match(Application.Max(colrng), colrng, 0) End Function Or with a formula - =MATCH(MAX(A:A),A:A,0) Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return row #
How about
maxRow = ActiveCell.End(xlDown).Row Regards Rowan "Bing" wrote: Hello, Is there an efficient way to return the row # corresponding to the row that contains the maximum value in a column range? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return row #
Gary,
This doesn't work if the range is a sub set of a column not starting at row 1. For instance, if rows 7,8,9 contain 100,200,300 your routine returns 3 not 9. This amendment fixes it Function MaxRow(colrng As Range) Dim idx As Long idx = Application.Match(Application.Max(colrng), colrng, 0) MaxRow = colrng.Cells(idx, 1).Row End Function -- HTH RP (remove nothere from the email address if mailing direct) "GaryDK" wrote in message oups.com... Hi Bing, In VBA you can do it like this - Sub GetMaxRow() MsgBox MaxRow(Range("A1").EntireColumn) End Sub Function MaxRow(colrng As Range) MaxRow = Application.Match(Application.Max(colrng), colrng, 0) End Function Or with a formula - =MATCH(MAX(A:A),A:A,0) Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return row #
Hi gentlemen.
Just wanted to say thanks very much for all of your helpful feedback. Cheers, Bing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return date if in range, else return blank | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
check if reference exists, then return its value or return 0 | Excel Worksheet Functions | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |