How to return row #

[Guest]

Hello,

Is there an efficient way to return the row # corresponding to the row that
contains the maximum value in a column range?

Thanks

 

[GaryDK]

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

 

[Guest]

How about

maxRow = ActiveCell.End(xlDown).Row

Regards
Rowan

 

[Bob Phillips]

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)

 

[Guest]

Hi gentlemen.

Just wanted to say thanks very much for all of your helpful feedback.

Cheers,
Bing