Max Non Blank cells in a column for range with more than 1 column

[drom]

]Hi and thanks in advance!

Say I have 10 columns with data in some cells
A1:J50

Range("A1:A50") may have 10 non blank cells
Range("A1:A50") may have 15 non blank cells
Range("B1:B50") may have 07 non blank cells
Range("C1:C50") may have 21 non blank cells
Range("D1:D50") may have 26 non blank cells
Range("E1:E50") may have 03 non blank cells
​

I need a formula to give me the Max number of cells non Blanks in a single column

In my case 26
I have created a dinamic range.

But I do not want to use:


referes to := MAX(Counta(A1:A50);Counta(B1:B50)...) etc...

 

[Marcelo Branco]

hmm... i think you need the last row with data in the Range(A1:J50) to create correctly the named range, not the Max of non-blank cells on each column.
Suppose column A has just one cell with data, say A50. If so, i suppose, the named range should be A1:J50 irrespective of the number of non-blank cells in the other columns. Am i right?

M.

 

[drom]

I was not thinking, but actuaslly you are right
If A50 is Non Blank then 50 is the Answer
Anyway suppose I am using a Table, actually I can't but, if column D goes until Row 26 then the Answer is 26, if Column F goes until F36 then the answer should be 36
any idea ?

 

[admiral100]

Hi,

Maybe this:-

Ctrl+Shift+Enter NOT just Enter

=MAX(IFERROR(--(A1:J50<>"")*ROW(A1:A500),0))

 

[Marcelo Branco]

Assuming your data are in Sheet1 to create the named range, say, MyRange, try

Formulas > Names Manager > New
Name: MyRange
Refers to: =Sheet1!$A$1:INDEX(Sheet1!$J$1:$J$50,MAX(INDEX((Sheet1!$A$1:$J$50<>"")*ROW(Sheet1!$A$1:$J$50),)))

Hope this helps

M.