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 Aladin Akyurek on October 26, 2001 12:54 PM
Do you mean last cell used in a particular column in a particular sheet?
Posted by CJR on October 26, 2001 1:04 PM
in this case i was looking for a column , but it would be nice to know how to do the sheet also
Posted by Aladin Akyurek on October 26, 2001 1:47 PM
If, say, column A in Sheet1 is a column where you enter numeric data frequently, the formula that follows will give you the row number of the last in use in A:
=MATCH(9.99999999999999E+307,A:A)
One caveat. The column must not have a number as big as 9.99999999999999E+307, unless a single instance of it is in the last cell.
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
will give you the reference of the last cell in use in A ('1' in the formula is the column number of A).
If the above is not satisfactory, use the following UDF that I've stolen & modified a bit. You need to insert this in a module in your workbook after activating Visual Basic Editor.
Function ENDCELL(rng As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, _
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Address
Exit Function
End If
Next i
End Function
Usage:
=ENDCELL(A:A)
This will produce the address/reference of the last cell in use in A.
Hope this helps.
Aladin
