brilliant - thank you (both)

out of interest, of the last two, is there any benefit in using an array
formula vs a non-array (in this instance)?

thanks

Tim

"Ron Coderre" wrote in message
...
Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):IND EX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A, 1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Tim" <tmarsh-trousers-@-take off my trousers to reply-blueyonder.co.uk
wrote in message ...
[sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will be
filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the maximum
number of cells filled in any one column (i don't need to know which
column)

Thanks for any guidance,

Tim