shortcut to last entry in a column

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

CTRL + DOWN


"inthestands" <[email protected]> wrote in message
news:[email protected]...
> 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
>

I think you want Control + End

"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
>

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
>

I am sorry, CTRL+Down only takes me to the next cell after a single open
cell. I need to get to the last cell. ( I am assuming down is the down
arrow).
--
inthestands


"JethroUK©" wrote:

> CTRL + DOWN
>
>
> "inthestands" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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(Sheet1!$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
> >

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(Sheet1!$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
> > >

My apologies, JMB....I meant to respond to the original post, not your post.

***********
Regards,
Ron

XL2002, WinXP


"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(Sheet1!$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
> > > >

No need to apologize, Ron.

"Ron Coderre" wrote:

> My apologies, JMB....I meant to respond to the original post, not your post.
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "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(Sheet1!$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
> > > > >

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(Sheet1!$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
> > > >

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(Sheet1!$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
> > > >

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(Sheet1!$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
> > > >

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(Sheet1!$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
> > > > >

try
Sub firsttolast()
lr = Cells(Rows.Count, "a").End(xlUp).Row
fr = Columns(1).SpecialCells(xlCellTypeConstants)(1).Row
Range(Cells(fr, 1), Cells(lr, 1)).Copy
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"Constantly Amazed" <Constantly [email protected]> wrote in
message news:[email protected]...
> 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(Sheet1!$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
>> > > >