need my formula to run to the bottom of my file
Posted by CJR on October 26, 2001 12:47 PM
i am trying to set up a formula that will find the bottom of my file each time i run it. the length of the file will change from day to day, sometime it might be shorter and sometime it might be longer. any ideas
Posted by Damon Ostrander on October 29, 2001 6:58 AM
Hi CJR,
You didn't mention whether you are working in VBA, or want an Excel formula, and in addition what it means to find the bottom of the file. Do you want, for example, the row number of the last row on a worksheet that contains data? Or perhaps the last row that has data in a certain column? Or perhaps the last row that has cell formatting? Or perhaps some data in the last row, or an object reference to the last row? Can you be more specific?
Whatever the question, the answer is quite easy. For example, if you want a VBA function that returns the number of the last row that contains data in a particular column, use this function:
Function LastRow( CellRef as Range ) as Long
' Returns the last row containing data in the
' column containing cell CellRef.
LastRow = Cells(65536,CellRef.Column).End(xlUp).Row
End Function
To use this function in Excel, enter the formula:
=LastRow(B1)
where B1 is provided to tell LastRow that you want it to find the last row containing data in column B.
Happy computing.
Damon
