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