Try this

=LOOKUP(2,1/(I1:I20<""),I1:I20)

--
__________________________________
HTH

Bob

"maninashed" wrote in message
...
I am trying to find the last occupied cell in a range.

I have tried every different method that I have found in this group
and others but none seem to work for me.

I have a range in which values are copied from another sheet with a
formula like:

=IF('Data entry'!A19<"",'Data entry'!A19,"")

This formula will copy the contents of the cell in the 'Data entry'
sheet if it is non-empty, otherwise it will place 'nothing' in the
cell.

My problem is that all of the 'find last occupied cell' routines I
have used refuse to see this cell as being unoccupied when it has
nothing (i.e. "") in it.

One thing I have tried is to create a column which has an =IF()
formula in it which counts how many cells have values in a given row
and if it is more than zero then it puts a 'Y' in that row. I then do
a Range.Search on that column to find the last occurance of 'Y'. Even
this won't work! It simply highlights the last cell in that column
with the =IF() formula in it - regardless of whether it has 'Y' in it
or not.

Any help gratefully appreciated.

Mark