Max Row Index for an Array

[ronjon65]

Is there a simple way to get the max row index (where the cell is not equal to "") of an array (such as B20:K100)?

I know I can loop through the rows and columns to find it, but that seems like more work than it needs to be?

[p45cal]

take a look here: http://www.cpearson.com/excel/LastCell.aspx

It might be something along the lines of:
LastRow = Range("B20:K100").Find(what:="*", after:=Range("B20:K100").Cells(Range("B20:K100").Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

(untested).

[ronjon65]

Well that must have been a good guess because it seems to work :)

Thanks for the help!

[p45cal]

Now that I can test, it gives the wrong answer if there is only one entry in the last row of the block in its very rightmost cell.
The solution is simple, change:
LastRow = Range("B20:K100").Find(what:="*", after:=Range("B20:K100").Cells(Range("B20:K100").Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
to:
LastRow = Range("B20:K100").Find(what:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

[ronjon65]

Thanks again, that works as well. I wasn't able to reproduce the error you mentioned, but changed it to the new code. Looks simpler anyway.

[snb]

or


msgbox cells(20,2).currentregion.rows.count+19