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!

--
Message posted via http://www.officekb.com

Try this one-liner...

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

Rick


"Michiel via OfficeKB.com" <u40062@uwe wrote in message
news:7f4b719872d1e@uwe...
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!

--
Message posted via http://www.officekb.com

Rick you are a genious!
Thanks!


Rick Rothstein (MVP - VB) wrote:
Try this one-liner...

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

Rick

--
Message posted via http://www.officekb.com