How to get the last row in a column without selecting

  • Thread starter Thread starter Michiel via OfficeKB.com
  • Start date Start date
M

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!
 
Try this one-liner...

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

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File Name changes with dates and Deleting Rows of Zeros 0
variable number of rows. 2
Selecting Till last row 1
Fill Down to Stop at Last Row 4
Accessing rows after AutoFilter 4
Macro to Define Name on a changing selection of cells 5
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Select all rows till last row with value in Column A and CopyPaste 7

Back
Top