You just need to find the first and last cell. The "parent" of the active
cell is the worksheet. Everything inside the With statement that is preceded
by a period refers back to the object described in the first line of the with
statement. You can specify a specific cell on the worksheet by using
Cells(Row, Column).

The first cell is found by checking to see if the cell in row 1 of the
activecell's column is empty. If not, that is the first cell. If so, it
finds the first cell with something in it (the end(xldown) method - it works
similar to you holding down the Control+DownArrow key). Repeat the process
to find the ending cell only starting from the bottom of the worksheet and
going up.

Sub LastCell()
Dim rngCell1 As Range
Dim rngCell2 As Range

With ActiveCell.Parent
If IsEmpty(.Cells(1, ActiveCell.Column)) Then
Set rngCell1 = .Cells(1, _
ActiveCell.Column).End(xlDown)
Else: Set rngCell1 = .Cells(1, _
ActiveCell.Column)
End If

If IsEmpty(.Cells(.Rows.Count, ActiveCell.Column)) Then
Set rngCell2 = .Cells(.Rows.Count, _
ActiveCell.Column).End(xlUp)
Else: Set rngCell2 = .Cells(.Rows.Count, _
ActiveCell.Column)
End If

.Range(rngCell1, rngCell2).Select
End With
End Sub

"Constantly Amazed" wrote:

Hi

I used the code which worked nicely.

How would I have to modify it if I wanted to select the first cell in the
column (parent cell ?) and then not only fmove to the last entry but also
select all cells inbetween ie ready for copying.

Regards



"JMB" wrote:

I usually go to a column a know is empty and hit Control+Down(arrow) to get
to the bottom of the sheet, then go over to the column I want and hit
Control+Up(arrow)

Or, if he wants to be able to go to the last cell of any column and doesn't
mind a VBA solution, I would put this in the Personal macro workbook and
attach it to a button on the toolbar, or assign a shortcut key.

Sub LastCell()
With ActiveCell.Parent
.Cells(.Rows.Count, _
ActiveCell.Column).End(xlUp).Select
End With
End Sub

"Ron Coderre" wrote:

Try something like this "trick":

Create a Dynamic Named Range:

<insert<name<define
Names in Workbook: aaa
Refers to:
=INDEX(Sheet1!$A$1:$A$65535,SUMPRODUCT(MAX((ROW(Sh eet1!$A$1:$A$65535))*(Sheet1!$A$1:$A$65535<""))))

That works if your entries will be in sporadic cells anywhere in Col_A. I
locates the last entry in Col_A with a value.
If your practical row limit is something less that 65,535 use a different
limit.
Note: That formula won't accept an entire column (eg $A:$A )

Now...to go directly to your last input:
Press the [F5] key (the shortcut for <edit<goto)
Type: aaa
Press [enter]

Not the most elegant solution, but it's quick to execute and involves no VBA.
Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"JMB" wrote:

sorry, Control+End is the last cell in the used range.

"inthestands" wrote:

I have data in every other row in a column from rows A1 to A12455. I would
like to know if there is a keyboard shortcut that takes me directly to the
last cell (A12455) in the column that has data in it.

Thanks in advance,

Ed