VBA Max of a column (2000)

Topic

New Reply

Why doesn’t
iMax = Application.WorksheetFunction.Max(Column(1))
work?
Is there a one-liner that does work? (w/o resorting to Evaluate)
TIA –Sam

Reply | Quote

Replies

Try this two liner
Columns(1).Select
imax = Worksheetfunction.Max(Selection)

Tom Duthie

Reply | Quote

That does not work and it violates
Sam’s Second rule of XL programming:
2) NEVER use or change the selection

But thanks anyway!

Reply | Quote

(Edited by mbarron on 05-Apr-07 14:26. edit 2 – got rid of extra =)

Try this one:

iMax = Application.WorksheetFunction.Max(Range(“A:A”))

or

iMax = Application.WorksheetFunction.Max(Cells(1, 2).EntireColumn)

Reply | Quote

Bizzare! That works! Thanks! I’d like to see an expalnation of why.

BTW, I resorted to some really weird code instead:
iMax = [Max(Sheet1!C:C]

I don’t do that very often because there is a lot of overhead; ie, don’t do it in a loop. But, for initializing your variables, it’s quick & dirty. Dirty, because if you move column C, your code no longer works.

Reply | Quote

You could also use named ranges if you are going to be moving columns.

imax = Application.WorksheetFunction.Max(Range(“sam”))

Reply | Quote

It should be

iMax = Application.WorksheetFunction.Max(Columns(1))

i.e. Columns instead of Column.

Columns is the collection of columns (in this case of the ActiveSheet since you haven’t specified what it belongs to).
Column returns the column number of a range that contains only one column. Column(1) makes no sense.

Reply | Quote

Reply | Quote

You can also just use this if you’re a lazy typist like me:
iMax = Application.Max(Columns(1))
FWIW.

Reply | Quote

iMax = Application.Max(Column(1))
It dosn’t work for me!

Reply | Quote

Columns instead of Column again…

Reply | Quote

iMax = Application.Max(Columns(1))

Reply | Quote