An old chestnut: finding the last cell in a range

  • Thread starter Thread starter Mark Hanley
  • Start date Start date
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
 
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
 
Don't multi post, you have an answer in your other post in worksheet functions
Click to expand...

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.
 
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
 
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
Click to expand...

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