Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

Since you already have the last cell adapt the following to get what you want
-

The following example displays four different representations of the same
cell address on Sheet1. The comments in the example are the addresses that
will be displayed in the message boxes.
(Source: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx)

Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address() ' $A$1
MsgBox mc.Address(RowAbsolute:=False) ' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2]


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

Thanks, but not quite what I'm looking for.

I do not know the address of the last cell. I need the spreadsheet to tell
my macro the address of the last non empty cell.

I have column of numbers that may have some empty cells, I need a way for a
macro to determine the last non empty cell in the column. I was hoping to
write a formula on the worksheet that the macro could reference so it would
know where to copy new information.

Or if a function is not possible, a VBA solution would be OK

Thanks


Mike
"Sheeloo" wrote:

Since you already have the last cell adapt the following to get what you want
-

The following example displays four different representations of the same
cell address on Sheet1. The comments in the example are the addresses that
will be displayed in the message boxes.
(Source: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx)

Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address() ' $A$1
MsgBox mc.Address(RowAbsolute:=False) ' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2]


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

Try this:

="D"&MATCH(REPT("z",255),D:D)


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

For a worksheet formula, you could do something like this...

="D"&MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)

For a macro solution (which, if you are in a macro, is the best way to get
this)...

LastCellInD = Cells(Rows.Count, "D").End(xlUp).Address(0,0)

--
Rick (MVP - Excel)


"mike in texas" wrote in message
...
Is it possible to return the address of the last non empty cell in a
column.
I've found lots of ways to return the value of the last cell, but I need
the
address e.g. D234.

Thanks

Mike

Thanks! Works perfectly.

"Teethless mama" wrote:

Try this:

="D"&MATCH(REPT("z",255),D:D)


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

Try the macro (Excel 2003)

Sub LastCellInColumn()
Range("D65536").End(xlUp).Select
MsgBox "Row Number = " & Selection.Row() & ", Column Number = " &
Selection.Column()
End Sub

"mike in texas" wrote:

Thanks, but not quite what I'm looking for.

I do not know the address of the last cell. I need the spreadsheet to tell
my macro the address of the last non empty cell.

I have column of numbers that may have some empty cells, I need a way for a
macro to determine the last non empty cell in the column. I was hoping to
write a formula on the worksheet that the macro could reference so it would
know where to copy new information.

Or if a function is not possible, a VBA solution would be OK

Thanks


Mike
"Sheeloo" wrote:

Since you already have the last cell adapt the following to get what you want
-

The following example displays four different representations of the same
cell address on Sheet1. The comments in the example are the addresses that
will be displayed in the message boxes.
(Source: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx)

Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address() ' $A$1
MsgBox mc.Address(RowAbsolute:=False) ' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2]


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

You're Welcome!


"mike in texas" wrote:

Thanks! Works perfectly.

"Teethless mama" wrote:

Try this:

="D"&MATCH(REPT("z",255),D:D)


"mike in texas" wrote:

Is it possible to return the address of the last non empty cell in a column.
I've found lots of ways to return the value of the last cell, but I need the
address e.g. D234.

Thanks

Mike

Hi,

You may also try to use the following array formula

="A"&MAX(IF(ROW($A$1:$A$11)*(A1:A11<""),ROW($A$1: $A$11)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mike in texas" wrote in message
...
Is it possible to return the address of the last non empty cell in a
column.
I've found lots of ways to return the value of the last cell, but I need
the
address e.g. D234.

Thanks

Mike