Anyone know how to get the maximum non-blank row number in a range of
columns? I am looking for an answer in a couple lines because i can
probably just do it by brute force using code like ,

for colx =1 to 5
maxrow=cells(65535,colx).end(xlendup)
if maxrow newmax then newmax=maxrow
next colx
newmax would of course be the maximum row number in the range of
columns 1-5.

But i was looking for something maybe in an array format formula? with
one line of code.(doesn't have to be an array though)
Thanks,
Chet

Hi Chet

I think that I would use same approch as you, with a little modification.

Dim MaxRow As Integer
For ColX = 1 To 5
If Range(Cells(65535, ColX)).End(xlUp).Row MaxRow Then _
MaxRow = Range(Cells(65535, ColX)).End(xlUp).Row
Next ColX

Regards,
Per

"Chet" skrev i meddelelsen
...
Anyone know how to get the maximum non-blank row number in a range of
columns? I am looking for an answer in a couple lines because i can
probably just do it by brute force using code like ,

for colx =1 to 5
maxrow=cells(65535,colx).end(xlendup)
if maxrow newmax then newmax=maxrow
next colx
newmax would of course be the maximum row number in the range of
columns 1-5.

But i was looking for something maybe in an array format formula? with
one line of code.(doesn't have to be an array though)
Thanks,
Chet

Hi,

Try this

Sub stantial()
Set myrange = Range("A:E")
mymax = WorksheetFunction.Max(myrange)
End Sub

If you need to you can trap for zero with an IF statement
If mymax=0 then

Mike

"Chet" wrote:

Anyone know how to get the maximum non-blank row number in a range of
columns? I am looking for an answer in a couple lines because i can
probably just do it by brute force using code like ,

for colx =1 to 5
maxrow=cells(65535,colx).end(xlendup)
if maxrow newmax then newmax=maxrow
next colx
newmax would of course be the maximum row number in the range of
columns 1-5.

But i was looking for something maybe in an array format formula? with
one line of code.(doesn't have to be an array though)
Thanks,
Chet