Ben McClave brought next idea :
Garry,

Thanks for the notes. I guess I shouldn't try to write code after dinner -
my brain must have been a bit foggy!

Ben

Ben,
I can appreciate your comment. I used to provide rather long,
self-documenting code that showed the steps fairly clearly. I now try
to provide code that's more efficient, but as self-documenting as
possible so readers can understand what it's doing.<g

Personally, I'd turn this sub into a reusable function that returns
either the row number when passed the column reference...

Function GetLastRow(vPos As Variant) As Long
GetLastRow = Cells(Rows.Count, vPos).End(xlUp).Row
End Function

...and use it like this...

Sub ShowLastCell()
Dim vRef As Variant, rng As Range
vRef = Application.InputBox("Enter a column letter or number", _
Type:=3) '//accept numbers or text only
If vRef = False Then Exit Sub '//user cancels

Set rng = Cells(GetLastRow(vRef), vRef)
MsgBox rng.Address: rng.Activate
End Sub
===

Conversely...

Function GetLastCol(lPos As Long) As Long
GetLastCol = Cells(lPos, Columns.Count).End(xlToLeft).Column
End Function
===

And finally...

Function GetLastCell(Optional Row&, Optional Col&, _
Optional IsRow As Boolean = True) As String
If Row = 0 Then Row = ActiveCell.Row
If Col = 0 Then Col = ActiveCell.Column
If IsRow Then
GetLastCell = Cells(Rows.Count, Col).End(xlUp).Address
Else
GetLastCell = Cells(Row, Columns.Count).End(xlToLeft).Address
End If
End Function

...where you can specify last cell in a row OR column and get its
address. Use it as follows:

Last row in ActiveCell column:
Range(GetLastCell()).Select

Last row in column3:
Range(GetLastCell(, 3)).Select
*OR*
Range(GetLastCell(Col:=3)).Select

Last column in ActiveCell row:
Range(GetLastCell(IsRow:=False)).Select

Last column in row3:
Range(GetLastCell(3, , False)).Select
*OR*
Range(GetLastCell(Row:=3, IsRow:=False)).Select

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion