# Get Max Value In Column Of Unknown Number of Rows

I'm not very good with Excel at all and I haven't found a specific answer for this. I'm looking for a simple formula to retrieve the max value in a particular column. I need it to begin searching from row 8, so for example =MAX(A8:"?"). I thought it would be * or something like that but it doesn't appear to be. I don't know what the last row is going to be because it will vary. How do I specify the 2nd variable in the formula to be the last row in the column without knowing beforehand?

-

## migrated from stackoverflow.comDec 7 '12 at 14:28

This question came from our site for professional and enthusiast programmers.

This solution locates the value of the last cell in the column which is partly what I need but I'm not quite sure how to dissect it to get just the last non-empty cell location within the column. – user1066133 Dec 7 '12 at 12:35

Try creating a "Dynamic named range". If you have continuous numeric data from A8 downwards then that can be defined as

=OFFSET(\$A\$8,0,0,COUNT(\$A\$8:\$A\$1048576))

[Excel will add in sheet names]

If you name that range Data then you can simply use

=MAX(Data)

The DNR may also come in useful for other calculations

In Excel 2007 or later versions You could also convert your data in to a table and then you can refer to a specific column of the table which will increase in size as the size of the table increases.

Some information on Dynamic named ranges here: http://www.contextures.com/xlNames01.html

-

Similar to Barry's solution, but a little cleaner (in my opinion).

=MAX(OFFSET(\$A:\$A,7,0,COUNT(\$A:\$A)))
-

It's ugly, but I'd use: =MAX(A8:A1048576)

-
Haha yeah I'm looking for ways to avoid this. I didn't think it'd be such a hassle to do something like this with such and advanced application. If I can't find a better solution I'll have to go with this. – user1066133 Dec 7 '12 at 12:32

Simple way in Excel:

=MAX(A8:A1048576)

Creating UDF in VBA. This allows for a dynamic offset and will sum the column it is placed in:

Function SumColumn(offset As Double)

Dim Data As Range

Set Data = Application.Caller.EntireColumn.Resize(Application.Caller.EntireColumn.Rows.Count - offset, 1).offset(offset, 0)

SumColumn = Application.WorksheetFunction.Sum(Data)

End Function
-