PDA

View Full Version : [SOLVED] Max Row Index for an Array



ronjon65
01-27-2016, 12:22 PM
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
01-27-2016, 02:52 PM
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
01-27-2016, 04:23 PM
Well that must have been a good guess because it seems to work :)

Thanks for the help!

p45cal
01-28-2016, 03:31 AM
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
01-28-2016, 11:28 AM
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
01-28-2016, 12:52 PM
or


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