I have heavily studied "how to determine the last row." There are several ways. Chip even has a page on it. What I don't get is, why the complexity? I have been unable to find a flaw in this:I'll give what I believe is the answer, which you experts can respond to and ignore the rest below. Is the issue that when people want "last row" then they usually want the last row with cell contents, as opposed to where Control-end lands?activesheet.usedrange ' required. Picked this up from Walkenbach's utils 20 years ago :) L = activesheet.cells.specialcells(xlCellTypeLastCell).row ' or .column
Everything below considers alternative methods of determining last row or column; not required for an expert to answer.
Some (maybe including Bob Umlas?) say the "best" way is to search for "*" using xlPrevious. Is that really necessary? Other methods involve End(xlUp) but that's column specific (and they can suffer if the very bottom row in a sheet is used). Chip says that the special cell gives the right row, but potentially wrong column. I've lived long enough to know that when I doubt Chip I always turn out to be wrong, but I've really tried and haven't gotten the incorrect column as he seems to suggest.
FYI, sht.UsedRange.Rows.Count is tempting but errs when row 1 is not used. A cute solution is
sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
which solves that problem but it seems awfully wordy. Another suggested solution is
sht.UsedRange.Rows.Count + sht.UsedRange.Rows(1).Row - 1
So why not just sht.cells.specialcells(xlCellTypeLastCell).row ? And .column ? Is there a circumstance that breaks it? And wouldn't it be the most efficient, most "built in" approach? Remember, I'm issuing "activesheet.usedrange" first.
A few pages about it -
Chip: http://www.cpearson.com/excel/LastCell.aspx
Several methods (read the comments too): http://www.thespreadsheetguru.com/bl...lumn-using-vba
Much detailed analysis: https://www.reddit.com/r/excel/comme...ow_in_a_sheet/
Bookmarks