Back to Forms in Excel VBA archive index

Back to archive home

Trying to find formula that will look at the LAST value in a column, not the MAXimum value. Any help would be very much appreciated.

Check out our Excel Resources | ||||

you can try this:

=OFFSET(A1,COUNT(A:A),0)

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).

Jack --

If col A is of numeric type,

=MATCH(9.99999999999999E+307,A:A)

will give you the row number of the last cell that contains a numeric value;

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

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))

End Function

and use:

=INDEX(A:A,SUMPRODUCT(MAX((LEN(USED(A:A))>0)*ROW(USED(A:A)))))

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.

Aladin

The way I did this was as follows:

=CONCATENATE("D",DCOUNTA(D:D,1,D:D)+1)

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

=CONCATENATE("D",DCOUNTA(D:D,1,D:D)+4)

Mike --

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.

Aladin

=========

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.