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.

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

Don't multi post, you have an answer in your other post in worksheet functions

"Mark Hanley" wrote:

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.

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

hi Mark,

To help us identify the problem, can you please tell us what methods
you have tried (ie post the code)?

Is your data entry typed in, or is it copy & pasted from elsewhere (eg
a webpage)?

What results do you get for each of the below formulae?
=LEN('Data Entry'!A19)
=ISBLANK('Data Entry'!A19)
=CHAR('Data Entry'!A19)
=CODE('Data Entry'!A19)


hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140972

Select the range
To get the location, run WhereIsIt
To get the value, run WhatIsIt

Sub WhereIsIt()
Dim r As Range, rr As Range
Dim addy As String
addy = ""
Set r = Selection
For Each rr In r
If IsEmpty(rr) Then
Else
addy = rr.Address
End If
Next
MsgBox addy
End Sub


Sub WhatIsIt()
Dim r As Range, rr As Range
Dim valu As String
valu = ""
Set r = Selection
For Each rr In r
If IsEmpty(rr) Then
Else
valu = rr.Value
End If
Next
MsgBox valu
End Sub

--
Gary''s Student

On 4 Oct, 14:26, Mike H wrote:
Don't multi post, you have an answer in your other post in worksheet functions



"Mark Hanley" wrote:
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.

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

Actually I didn't multi-post. I created two separate posts (albeit
with *almost* identical content).

I posted a very similar message to this group after I found it and
considered that it was more suited to my problem. By the time I had
finished the post to this group I had received replies from the other
group.

As it is, I found a solution that worked from the formulas group:
http://groups.google.co.uk/group/mic...5adaad0?hl=en#

Thank you to everybody who took the time to reply.

That is multi-posting..............two separate posts with same subject
matter.

Maybe you are thinking you did not "cross-post", which is true.

Crossposting is preferred to multi-posting.

Those of us using real news readers can deal with cross-posting by simply
not downloading more than one copy of a cross-post.


Gord Dibben MS Excel MVP

On Sun, 4 Oct 2009 08:17:24 -0700 (PDT), maninashed
wrote:

Actually I didn't multi-post. I created two separate posts (albeit
with *almost* identical content).

On Sun, 4 Oct 2009 06:07:06 -0700 (PDT), Mark Hanley
wrote:

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.

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

Something like:

=LOOKUP(2,1/(A:A<""),A:A)

Note that in versions of Excel prior to 2007, you may not be able to reference
the entire row. If that is the case, then:

=LOOKUP(2,1/(A1:A65534<""),A1:A65534)

or similar.
--ron

Just to add on...

Happy to here that you have found the solution from the responses at
worksheetfunctions; however if you have responded to the answer at
WorksheetFunctions and to this post the confusion could have been avoided..


"Gord Dibben" wrote:

That is multi-posting..............two separate posts with same subject
matter.

Maybe you are thinking you did not "cross-post", which is true.

Crossposting is preferred to multi-posting.

Those of us using real news readers can deal with cross-posting by simply
not downloading more than one copy of a cross-post.


Gord Dibben MS Excel MVP

On Sun, 4 Oct 2009 08:17:24 -0700 (PDT), maninashed
wrote:

Actually I didn't multi-post. I created two separate posts (albeit
with *almost* identical content).