How to find maximum non-blank row number without brute force method?
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 |
How to find maximum non-blank row number without brute force method?
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 |
How to find maximum non-blank row number without brute force metho
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 |
All times are GMT +1. The time now is 12:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com