finding Last row ??

[Colin Legg]

Like you, I tend to use Range.Find(). It's normally an option to remove any filtering before I call it. Other than filtering, another thing Range.Find() will miss is if the last cell only contains a prefix character, '. Taking those into account too, I don't think there is a 100% foolproof method to determine the last row without some sort of looping involved.

There's a temptation to try using Range.SpecialCells() [xlCellTypeConstants and xlCellTypeFormulas] and to derive the last cell from those, but there could be problems on large worksheets because the Range.SpecialCells() method has a limitation of 2^13 non-contiguous cells.

 

[HalfAce]

For what it's worth, the most reliable & consistent method of determining the last used row (or column) I have seen uses .Find (filtered or not) like so:

Dim LstRw As Long, LstCol As Integer
LstRw = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LstCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

This seems to work anytime, anywhere and is not effected by 'phantom' used cells (such as values & formulas that have been deleted) which are sometimes still seen as a used range by excel.
I don't remember who showed this to me (it was years ago) but I think it may have been Tom Urtis

Hope it helps.

 

[Oorang]

There is a pretty exhaustive discussion of this topic here. It's a long thread about a lot of different stuff. But I do think the best way to accomplish this task (and the discussion of why) was hit on here.

Public Function LastRowInSheet(wks As Worksheet) As Long 
     'From Rorya on MrExcel:
     'http://www.mrexcel.com/forum/showthread.php?p=1793851&posted=1#post17938521
     ' Returns the number of the last row with data anywhere in it
    LastRowInSheet = 1 
    On Error Resume Next 
    With wks.UsedRange 
        LastRowInSheet = .Cells.Find("*", .Cells(1), SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious).Row 
    End With 
End Function

 

[snowblizz]

Interesting thread. It is something that pops up many times a day, and I *never* remember the same syntax twice.

Though that immediately sparks a follow up question.
Would you give a "good enough" answer or try and fit in the "perfect" one.

The End(xlUp) is fairly easy to understand, and quite often sufficient.Usually one can say with a certain degree of accuracy if a suggestion will be good enough to solve the problem. I usually avoid deviating too far from what someone has already posted, if at all posssible, as that's usually where their comfort level is.

 

[Tom Urtis]

As I inch my way towards filing out the content on my web site, these 3 links might help anyone who's interested in finding the last row, column, or used range under various scenarios:

How to know the last row or last column:
http://www.atlaspm.com/faqvbalastrow.html#61

Address of the sheet's used range:
http://www.atlaspm.com/faqvbausedrange.html#63

Address of used range when teh start and end cells are all unknown:
http://www.atlaspm.com/faqvbaunknownrange.html#65

 

[Michael M]

Hmm, it seems my question did indeed, have many different thoughts from others and I will try some of the other ways of "finding the last cell"

Tom
I visited your website and I think it is going to provide a lot of assistance to people like myself, that always seem to have to "go find" the snippet I need, even though I have thousands of lines of my own, I never seem to have the couple of lines I want.

I look forward to visiting it again when it's completed.

Regards
Michael M