Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
CTRL + DOWN
"inthestands" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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(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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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(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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
shortcut to last entry in a column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Row filtering based on input box entry (column heading) | Excel Worksheet Functions | |||
Running total w/2 columns - Excel | Excel Worksheet Functions |