How to find maximum non-blank row number without brute force method?

[Chet]

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

 

[Per Jessen]

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

 

[Mike H]

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