|Check out our Excel Resources|
This is assuming that the column does not have any blanks in it, and that the data is in column A and begins in A1 (adjust as needed).
If col A is of numeric type,
will give you the row number of the last cell that contains a numeric value;
will give you the last numeric value.
If col A is of mixed data type (that is, A houses numbers as well as text) and you want the last value whether its data type is numeric or text, then add the following UDF to your workbook:
Function Used(r As Range) As Range
' Harlan Grove
' Sun 25 Nov 01
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
Note 1. The above formulas are not affected by blanks.
Note 2. They will not return a formula generated blank that is the last value.
The way I did this was as follows:
This assumes that the column is full from D1, and has no blank cells.
The above will return D24 is D24 is the last cell that contains a value. Note that it will NOT count blank cells, and thus the number will be off by the number of blank cells in the column.
If you have 3 blank cells, and you know you will always have 3 blank cells, then make the formula
DCOUNTA as well as COUNTA always suffer from the potential existence of blanks in the range of interest. Moreover, COUNTA is inefficient compared to MATCH (as I use in my reply) and it's a volatile function that leads to recals.