Thread: Do Until / Loop code to find max value in many adjacent columns?

Welcome to the forum.

One way:

Sub MAXVALUES()
    Dim r As Range
    Dim lCol As Integer
    Dim iCol As Integer
    
    Set r = Intersect(ActiveSheet.UsedRange, Rows("9:" & Rows.Count))
    lCol = ActiveSheet.UsedRange.Columns.Count
 
    For iCol = 1 To lCol
        Cells(2, r.Columns(iCol).Column) = WorksheetFunction.Max(r.Columns(iCol))
    Next iCol
End Sub

The code considers the fact that the used range does not always include A1.

If there are no numeric values above row 9 then you could also use entire column references in the MAX.
The MAX function will only use the Intersect of the Used Range and the precedent range (unlike SUMPRODUCT / Arrays).

edit: just to be clear - I'm not saying you *should* ... the above is more to do with how MAX works (in terms of worrying about last row etc...)

If there are no numeric values above row 9 then you could also use entire column references in the MAX.

My thought was that there might be values in row 2 from a previous invocation.

But on that topic, DO, do you have a list (or reference to) the functions that clamp to the used range?

Originally Posted by shg

DO, do you have a list (or reference to) the functions that clamp to the used range?

Unfortunately not... my belief is that this holds true for the majority of functions with the exception of SUMPRODUCT & Arrays and things like LOOKUP(2,1/...) & INDEX(...,0) etc...

I've seen Charles Williams demo/test by comparing the calc. time of a given function using entire column & specific range precedents - and showing results to be the same (using his own precision tools of course).

This is one of the reasons I'm wary of advocating DNR's when specifically used to restrict range references (I know you like them for other reasons like visibility).
A lot of functions are so efficient the overhead in establishing the "used range" is not always necessary.

DNR = Do Not Resuscitate, as in, Can this formula be saved?

to be specific... Dynamic Named Ranges

there was a great (and I mean great) exchange a while back at MrExcel involving Charles W:

http://www.mrexcel.com/forum/showthread.php?t=371570

(do I get infracted for posting that link ?)

edit:

Originally Posted by shg

My thought was that there might be values in row 2 from a previous invocation.

D'oh !

Thanks for the linkm, that was interesting. It did not surprise me that Excel has no concept of 'used column' vs 'used range;' there's nothing in the object model to suggest otherwise.

(do I get infracted for posting that link ?)

I posted a link yesterday from there to here for an OP who wanted to post a file for me to look at. So far, I haven't been shot ...

I took the liberty of emailing Charles Williams regards "the function list" and he has kindly replied as follows:

Originally Posted by Charles Williams

I don't have a list -

In theory its only functions that need a built-in iterator like SUMPRODUCT and Array formulae that have a problem, but of course there may well be some long-standing bugs where other functions are not correctly programmed.
INDEX, VLOOKUP, SUMIFS are OK (although some of the weirder uses of INDEX might not be), don't know about LOOKUP.
UDFs need to be explicitly programmed.

Sub snb()
  sq = Cells(2, 1).Resize(, 3)
  For j = 1 To UBound(sq, 2)
    sq(1, j) = Application.Max(Cells(10, 1).Resize(5000).Offset(, j - 1))
  Next
  Cells(2, 1).Resize(, 3) = sq
End Sub

And if you want to take the usedrange per column into account you could use a UDF:

in A2: =max(snb(9,column()))

Function snb(r, c)
  snb = Cells(r, c).Resize(Cells(Rows.Count, c).End(xlUp).Row - (r - 1))
End Function