An old chestnut: finding the last cell in a range

  • Thread starter Thread starter maninashed
  • Start date Start date
M

maninashed

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
 
Hi,

You don't actually say what you want, is it the row number or the value, is
it text or numeric:. here are a few to have a look at

Last value, text or number
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))

Last numeric
=LOOKUP(9.99999999999999E+307,A:A)

Row number of last numeric
=MATCH(9.99999999999999E+307,A:A)

Row number last text
=MATCH(REPT("z",255),A:A)

Do any of those help?

Mike


Row number
 
Hi Bob

The formula I'm using is:

=IF(COUNT(D21,F21,H21)>0,"Y","")

Thanks for your time.

Mark
 
Hi Mike

Genius! I actually wanted the row number and I can use...

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

to find it and put it in a cell which my macro can then read.

Thank you very much for your time.

Mark
 
Glad I could help but if you want the row number to use in a macro then there
are VB methods that avoid the need to use worksheet functions.

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Returns the last used row of column A

Mike
 
Glad I could help but if you want the row number to use in a macro then there
are VB methods that avoid the need to use worksheet functions.

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Returns the last used row of column A

Mike
Click to expand...

Thanks Mike. I did try this and it just wouldn't work with my sheet. I
searched for a 'Y' but it still returned the last cell in the range
regardless of whether it had a 'Y' in it or not.
 
What about this line of code then...

LastRow = Columns("A").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

--
Rick (MVP - Excel)


Glad I could help but if you want the row number to use in a macro then
there
are VB methods that avoid the need to use worksheet functions.

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Returns the last used row of column A

Mike
Click to expand...

Thanks Mike. I did try this and it just wouldn't work with my sheet. I
searched for a 'Y' but it still returned the last cell in the range
regardless of whether it had a 'Y' in it or not.
 
Just loop up until it is a Y

For i = lastrow to 2 Step -1

If cells(i, "A").Text = "Y" Then

MsgBox "Found in row " & i
Exit For
End If
Next i

--
__________________________________
HTH

Bob

Glad I could help but if you want the row number to use in a macro then
there
are VB methods that avoid the need to use worksheet functions.

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Returns the last used row of column A

Mike
Click to expand...

Thanks Mike. I did try this and it just wouldn't work with my sheet. I
searched for a 'Y' but it still returned the last cell in the range
regardless of whether it had a 'Y' in it or not.
 
Try the below...By default it will look for xlPart..If you are looking for a
whole cell match specify that ..

Set rngtemp = Cells.Find(What:="Y", SearchDirection:=xlPrevious)
If Not rngtemp Is Nothing Then MsgBox "LastRow : " & rngtemp.Row

If this post helps click Yes
 
Back
Top