Find cell address of last cell in a column with text

[mike in texas]

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

 

[Sheeloo]

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/library/aa174749(office.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]

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

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/library/aa174749(office.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]

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

 

[Teethless mama]

Try this:

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

 

[Rick Rothstein]

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)

 

[mike in texas]

Thanks! Works perfectly.

Try this:

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

 

[Sheeloo]

Try the macro (Excel 2003)

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

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

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/library/aa174749(office.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]

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

 

[Teethless mama]

You're Welcome!

Thanks! Works perfectly.

 

[Ashish Mathur]

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