Home 
Search 
Today's Posts 
#1




How do i find last not empty cell in a range
My senario (xl2003)
B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#2




How do i find last not empty cell in a range
Perhaps:
Function lastone(r As Range) As String For Each rr In r If IsEmpty(rr) Then Else lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False) End If Next End Function  Gary''s Student  gsnu200822 "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#3




How do i find last not empty cell in a range
Hi,
There is a problem here  C10 is the last row with a non empty cell but column E is the last column with a non empty cell. And what do you want to do if C10, E10 and A10 are all non empty. The formula for the last nonempty cell in a specific column is =ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN()) This assumes you are putting the formula into the same column as the cells you are checking. If not change the last argument to read COLUMN(B1)  If this helps, please click the Yes button Cheers, Shane Devenshire "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#4




How do i find last not empty cell in a range
thanks for reply Gary
but supose it was a wery large range to test that why i dont wana use a loop or as u do for each "Gary''s Student" skrev: Perhaps: Function lastone(r As Range) As String For Each rr In r If IsEmpty(rr) Then Else lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False) End If Next End Function  Gary''s Student  gsnu200822 "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#5




How do i find last not empty cell in a range
thanks for reply Shane
yes it is cell C10 i wana find or in another word which column have data in the highest row in my shenario i dont no which column i have to look in that what the formula have to figure out sry my french "Shane Devenshire" skrev: Hi, There is a problem here  C10 is the last row with a non empty cell but column E is the last column with a non empty cell. And what do you want to do if C10, E10 and A10 are all non empty. The formula for the last nonempty cell in a specific column is =ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN()) This assumes you are putting the formula into the same column as the cells you are checking. If not change the last argument to read COLUMN(B1)  If this helps, please click the Yes button Cheers, Shane Devenshire "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#6




How do i find last not empty cell in a range
=MAX(IF(A1:F500<"",ROW(A1:F500)))  returns 10 (row)
=MAX(IF(A10:F10<"",COLUMN(A10:F10)))  returns 3 (column C) So i need a way to put these together as 1 formula and then put this in a address formula how ?? "excelent" skrev: thanks for reply Shane yes it is cell C10 i wana find or in another word which column have data in the highest row in my shenario i dont no which column i have to look in that what the formula have to figure out sry my french "Shane Devenshire" skrev: Hi, There is a problem here  C10 is the last row with a non empty cell but column E is the last column with a non empty cell. And what do you want to do if C10, E10 and A10 are all non empty. The formula for the last nonempty cell in a specific column is =ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN()) This assumes you are putting the formula into the same column as the cells you are checking. If not change the last argument to read COLUMN(B1)  If this helps, please click the Yes button Cheers, Shane Devenshire "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#7




How do i find last not empty cell in a range
Not sure why you only want the address, especially if you are going to do
another formula built around it (there is usually a better way for that scenario than creating the address); however, to answer your question, give this arrayentered** formula a try... =ADDRESS(MAX((A1:F6000<"")*ROW(A1:A6000)),MAX(IF( INDIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F "&MAX((A1:F6000<"")*ROW(A1:A6000)))<"",COLUMN(IN DIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F"& MAX((A1:F6000<"")*ROW(A1:A6000))))))) **Commit formula using Ctrl+Shift+Enter and not just Enter by itself Note: Change all occurrences of 6000 to the maximum row number you ever expect to have data in.  Rick (MVP  Excel) "excelent" wrote in message ... My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#8




How do i find last not empty cell in a range
Rick this is a wery nice job thanks alot
"Rick Rothstein" skrev: Not sure why you only want the address, especially if you are going to do another formula built around it (there is usually a better way for that scenario than creating the address); however, to answer your question, give this arrayentered** formula a try... =ADDRESS(MAX((A1:F6000<"")*ROW(A1:A6000)),MAX(IF( INDIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F "&MAX((A1:F6000<"")*ROW(A1:A6000)))<"",COLUMN(IN DIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F"& MAX((A1:F6000<"")*ROW(A1:A6000))))))) **Commit formula using Ctrl+Shift+Enter and not just Enter by itself Note: Change all occurrences of 6000 to the maximum row number you ever expect to have data in.  Rick (MVP  Excel) "excelent" wrote in message ... My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#9




How do i find last not empty cell in a range
Got it now  1 liners
Find's leftmost last not empty Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(1).Select Find's second leftmost last not empty Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(2).Select "Gary''s Student" skrev: Perhaps: Function lastone(r As Range) As String For Each rr In r If IsEmpty(rr) Then Else lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False) End If Next End Function  Gary''s Student  gsnu200822 "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
#10




How do i find last not empty cell in a range
Need to be a little carefull:
Sub lastone() Dim i As Long i = Selection.SpecialCells(xlCellTypeLastCell).Row MsgBox (i) End Sub will return the last row in UsedRange, even if Selection is only a subset of UsedRange.  Gary''s Student  gsnu200822 "excelent" wrote: Got it now  1 liners Find's leftmost last not empty Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(1).Select Find's second leftmost last not empty Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(2).Select "Gary''s Student" skrev: Perhaps: Function lastone(r As Range) As String For Each rr In r If IsEmpty(rr) Then Else lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False) End If Next End Function  Gary''s Student  gsnu200822 "excelent" wrote: My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a celladdress C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Find empty cell then stop  Excel Discussion (Misc queries)  
How to: Find first empty cell in column  Excel Worksheet Functions  
find last none empty cell  Excel Discussion (Misc queries)  
Find an empty cell and put a dash in it?  Excel Worksheet Functions  
Find a empty cell in next column  Excel Discussion (Misc queries) 