Jealousy

[RoryA]

but that's why you would use:

Cells(rows.Count, 1).End(xlUp)

- no version issues.

 

[Colin Legg]

You could use Rows.Count and then it would be compatible with all versions. The drawback with End(xlUp) is it will return incorrectly if the last row is hidden. If this is a possibility I use Find.

 

[Oorang]

but that's why you would use:

Cells(rows.Count, 1).End(xlUp)

- no version issues.

That still assumes you have correctly guessed which column is going to be the "longest"... See also Colin's remark. I hadn't even thought of that one

 

[RoryA]

I only use that in simple instances. My default function is:

Public Function LastRowInSheet(wks As Worksheet) As Long
   ' Returns the number of the last row with data anywhere in it
   LastRowInSheet = 1
   On Error Resume Next
   With wks.UsedRange
      LastRowInSheet = .Cells.Find(what:="*", after:=.Cells(1), _
              SearchOrder:=1, _
              SearchDirection:=2).Row
   End With
End Function

 

[Greg Truby]

Shoot, if I were needin' ta fetch that from a ws where didn't know the layout and hence which column I ought to be checkin', I would just dust off the [very] oldie but still quite goodie UDF for lastcell and get the row. To Rory's point, UsedRange is too easily sullied for it to me to use it in code that must be very robust. (And yes, you can tell this is something I wrote/copied years and years ago because I did NOT use Hungarian notation. :wink

Sub foo()
    MsgBox udfLastCell(ActiveSheet).Row
End Sub
Function udfLastCell(ws As Worksheet) As Range
    Dim LastRow&, LastCol%
    LastRow = 1: LastCol = 1
    '// Error-handling is here in case there is not any
    '// data in the worksheet
    On Error Resume Next
    With ws
        '// Find the real last row
        LastRow = .Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
        '// Find the real last column
        LastCol = .Cells.Find(What:="*", _
            SearchDirection:=xlPrevious, _
            SearchOrder:=xlByColumns).Column
    End With
    Set udfLastCell = ws.Cells(LastRow, LastCol)
End Function

But much more importantly, Mole has given us this following to work with:

Measure in Earth standard days, obviously the rotational period of any other place would knacker a watch as Arthur Dent found out

And this begs the question, will future versions of Excel support the probability functions needed to run Infinite Improbability Drives?

 

[Oorang]

hmmm... That may be better (trundles off to other thread to see that they think)

And now I am forced to ask... Do you carry that sledgehammer around in your pocket?


Edit: Missed Greg's post... but I still vote Rorya at this time.

Edit2: The other thread.

 

[Greg Truby]

And now I am forced to ask... Do you carry that sledgehammer around in your pocket?

Ahem, that ain't a sledgehammer, and my eyes are up here, mister...

 

[Oorang]

Ahem, that ain't a sledgehammer, and my eyes are up here, mister...

Aaaannnnd now I feel dirty. Besides I was looking at rorya's pant's..... I mean uhm, never mind.

 

[RoryA]

What pants?

 

[Greg Truby]

What pants?

gdammit Archibald. I was sippin' my coffee when I read that. Damñed near got coffee both up my nose and on the keyboard.