An old chestnut: finding the last cell in a range
 

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

An old chestnut: finding the last cell in a range
 

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

An old chestnut: finding the last cell in a range
 

On the second part, what are you using as a formula?



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

An old chestnut: finding the last cell in a range
 

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:A 1000<""))))

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



"maninashed" 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 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

An old chestnut: finding the last cell in a range
 

Hi Bob

The formula I'm using is:

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

Thanks for your time.

Mark

An old chestnut: finding the last cell in a range
 

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


On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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



"maninashed" 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 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

An old chestnut: finding the last cell in a range
 

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

"maninashed" wrote:

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


On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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



"maninashed" 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 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

An old chestnut: finding the last cell in a range
 

On 4 Oct, 14:54, Mike H wrote:
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



"maninashed" wrote:
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

On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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

"maninashed" 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 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

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.

An old chestnut: finding the last cell in a range
 

What about this line of code then...

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

--
Rick (MVP - Excel)


"maninashed" wrote in message
...
On 4 Oct, 14:54, Mike H wrote:
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



"maninashed" wrote:
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

On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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

"maninashed" 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 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

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.

An old chestnut: finding the last cell in a range
 

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

"maninashed" wrote in message
...
On 4 Oct, 14:54, Mike H wrote:
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



"maninashed" wrote:
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

On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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

"maninashed" 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 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

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.

An old chestnut: finding the last cell in a range
 

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
---------------
Jacob Skaria


"maninashed" wrote:

On 4 Oct, 14:54, Mike H wrote:
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



"maninashed" wrote:
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

On 4 Oct, 14:02, Mike H wrote:
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:A 1000<""))))

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

"maninashed" 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 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

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.