How to find last filled in cell in a column?

C

Cat Chowdy

I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell in
a specific column?

Thanks,
Cat
 
G

Gord Dibben

Cat

From Bob Phillips.......=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)


Gord Dibben MS Excel MVP
 
C

Cat Chowdy

Thanks! It works great. But can you explain to me what is happening.
More specifically I don't understand 1/(A1:A65535<>"").

Cat
 
G

Gord Dibben

Biff has provided a link to Bob Phillips site.

Bob shows many diverse ways to return last values.

The particular one I posted is on that page.

Here is Bob's explanation...........

This formula uses LOOKUP in its vector syntax form, with the lookup value as the
first parameter, the lookup vector as second, and the result vector as the last
parameter

The most interesting part of this formula is the lookup vector (the 2nd
parameter). The formula element
1/(1-ISBLANK(A1:A65535))
in this example returns the following array
{1;1;1;1;1;1;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;...;#DIV/0!}

that is, the ISBLANK function returns an array of TRUE (blank cell) or FALSE
(non-blank cell) values.

Subtracting this from 1 converts the array to an array of 0 (blank) or 1
(non-blank) values.

Dividing 1 by this array then returns an array of #DIV/0 (blank) or 1
(non-blank) values

The LOOKUP searches for the value '2' within the array (which now consists only
of '1' and #DIV/0 values). The LOOKUP will not find this value, so it matches
the last value that is less than or equal to lookup value. This is the last '1'
within the range which represents the last filled cell

This type of formula can be used for a lot of similar problems using the second
parameter to create a lookup vector consisting of either '1' or '#DIV/0' errors
by setting the Boolean expression accordingly.

Constraints:

In the column variation, the ISBLANK function and the result vector have to be
passed an explicit range, it does not work with a complete column

If the entire column / row is empty this formula will return an #N/A error as
the LOOKUP function finds no value that is smaller or equal to the lookup value
(the lookup vector consists only of #DIV/0 errors)

This formula is quite slow

This solution ignores error values. If the range contains, for example, #DIV/0
errors the formula will not highlight this fact, but will return the last
non-error value.


Gord

Thanks! It works great. But can you explain to me what is happening.
More specifically I don't understand 1/(A1:A65535<>"").

Cat
Click to expand...

Gord Dibben MS Excel MVP
 
C

Cat Chowdy

Thanks much Gord and Biff. I have been programming for some time, but have
never done much in Excel before. I was not aware that it would return the
last "less than" match if it didn't find an exact match. Also, I would have
thought all the #DIV/0!'s would have produced an error.

Thanks!
Cat
 
Top