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 cell-address 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 ?

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 cell-address 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 ?

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 non-empty 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 cell-address 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 ?

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 cell-address 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 ?

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 non-empty 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 cell-address 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 ?

=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 non-empty 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 cell-address 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 ?

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 array-entered** 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 cell-address 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 ?

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 array-entered** 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 cell-address 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 ?

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 cell-address 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 ?

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 sub-set
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 cell-address 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 ?