How to get the last row in a column without selecting

[Michiel via OfficeKB.com]

Hi,

My aim is to get the last populated row in a column. I successfully use this
function.

Function fncLastRowCo(ColID As Long) As Long
'Returns the row number of the last row in the ColID-th column
Dim n As Long

Cells(1, 1).Select
ActiveCell.SpecialCells(xlLastCell).Select
n = ActiveCell.Row
Cells(n, ColID).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
fncLastRowCo = Selection.Row
End Function

Now I need to write a user defined function that can get the last row in a
column in any worksheet.
When one uses the function in E.g. Sheet2 and one needs to get the data from
Sheet5 the methode above is not possible since it is not possible to leave
the sheet on which the User Defined function is located, while running it.

Any ideas?

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

Try this one-liner...

LastUsedRowInCol4= Worksheets("Sheet5").Cells(Rows.Count, 4).End(xlUp).Row

Rick

 

[Michiel via OfficeKB.com]

Rick you are a genious!
Thanks!

Try this one-liner...

LastUsedRowInCol4= Worksheets("Sheet5").Cells(Rows.Count, 4).End(xlUp).Row

Rick