An old chestnut: finding the last cell in a range


M

Mark Hanley

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
 
Ad

Advertisements

G

Gary''s Student

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
 
M

maninashed

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

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/mi.../browse_thread/thread/6ddbd14f25adaad0?hl=en#

Thank you to everybody who took the time to reply.
 
G

Gord Dibben

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
 
R

Ron Rosenfeld

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
 
Ad

Advertisements

J

Jacob Skaria

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top