Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The size chestnut. | Excel Discussion (Misc queries) | |||
Finding the position of the i-th non blank cell in a vertical range | Excel Worksheet Functions | |||
Finding Cell Outside Of Range | Excel Worksheet Functions | |||
Finding max array value of variable cell range | Excel Discussion (Misc queries) | |||
Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) |