Selecting the first cell of the last row in a range

A co-worker asked me how to programmatically select the first cell of the
last row in a range in Excel 2003 (Windows XP). We found the Help reference
to selecting the last cell in a range, but we couldn't come up with a good
solution for the first cell in the last row, other than a "SendKeys" type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**

This will find the last row on the active sheet and then select col A in
that row.

Sub firstcelloflastrow()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
Cells(lr, 1).Select
'or
'Cells(lr, 1).End(xlToRight).Select
'to goto the first non blank cell on THAT row

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Susan Ramlet" wrote in message
...
A co-worker asked me how to programmatically select the first cell of the
last row in a range in Excel 2003 (Windows XP). We found the Help
reference
to selecting the last cell in a range, but we couldn't come up with a good
solution for the first cell in the last row, other than a "SendKeys" type
of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**

Could you explain why you would want to select the first used cell in a
range or is it the first cell in a range?

Susan Ramlet;295213 Wrote:
A co-worker asked me how to programmatically select the first cell of
the
last row in a range in Excel 2003 (Windows XP). We found the Help
reference
to selecting the last cell in a range, but we couldn't come up with a
good
solution for the first cell in the last row, other than a "SendKeys"
type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82520

Thank you, Don! We will put this to use. I appreciate the quick and complete
response.

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


"Don Guillett" wrote in message
...
This will find the last row on the active sheet and then select col A in
that row.

Sub firstcelloflastrow()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
Cells(lr, 1).Select
'or
'Cells(lr, 1).End(xlToRight).Select
'to goto the first non blank cell on THAT row

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Susan Ramlet" wrote in message
...
A co-worker asked me how to programmatically select the first cell of the
last row in a range in Excel 2003 (Windows XP). We found the Help
reference
to selecting the last cell in a range, but we couldn't come up with a
good
solution for the first cell in the last row, other than a "SendKeys" type
of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**

He wants to select the first cell of the last row of a range and then format
it programmatically (I don't know the specific objective in the context of
the overall spreadsheet; sorry...).

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


"Simon Lloyd" wrote in message
...

Could you explain why you would want to select the first used cell in a
range or is it the first cell in a range?

Susan Ramlet;295213 Wrote:
A co-worker asked me how to programmatically select the first cell of
the
last row in a range in Excel 2003 (Windows XP). We found the Help
reference
to selecting the last cell in a range, but we couldn't come up with a
good
solution for the first cell in the last row, other than a "SendKeys"
type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82520

Selecting is NOT necessary
Sub firstcelloflastrow()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr

Cells(lr, 1).numberformat=???
or
format(Cells(lr, 1), "mm/dd")
'or
'Cells(lr, 1).End(xlToRight).Select
'to goto the first non blank cell on THAT row

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Susan Ramlet" wrote in message
...
He wants to select the first cell of the last row of a range and then
format it programmatically (I don't know the specific objective in the
context of the overall spreadsheet; sorry...).

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


"Simon Lloyd" wrote in message
...

Could you explain why you would want to select the first used cell in a
range or is it the first cell in a range?

Susan Ramlet;295213 Wrote:
A co-worker asked me how to programmatically select the first cell of
the
last row in a range in Excel 2003 (Windows XP). We found the Help
reference
to selecting the last cell in a range, but we couldn't come up with a
good
solution for the first cell in the last row, other than a "SendKeys"
type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=82520