Let A be the column of interest.On 2002-07-20 01:20, beachboy wrote:

Hi,

First of all, thankyou to all, for your valuable replies and solutions to do things a number of different ways on my two previous posts.

I need to be able to find the cell reference nos of the first blank cell & last non blank cell in a particular column.

Is there a function to do this or ways to do this??

Thanks.

If A is of numeric type, use

[1]

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

If A is of type text, use

[2a]

=MATCH("*",A:A,-1)

unless A contains * as value; Otherwise, use

[2b]

=MATCH(REPT("z",50),A:A)

unless the last cell might house a formula computed "".

[1] (or [2]) gives you the location/position of the last non-blank/used cell in A.

Let B1 house either [1] or [2].

=OFFSET(A1,B1-1,0,1,1)

will give you the value in the last used cell.

The location/position of the first blank/empty cell can be computed with:

=MIN(IF(LEN(OFFSET(A1,0,0,B1-1,1))=0,ROW(OFFSET(A1,0,0,B1-1,1))))

which must be array-entered by hitting control+shift+enter at the same time, not just enter. B1, by the way, must house either [1] or [2].

Aladin

## Like this thread? Share it with others