Thread: Hotkey to mark all cells from current to last/first of column ?

Hi folks.

Do you know a hotkey which lets me mark all cells from the current one to the last/first of column ?
In the helpfile there are all sorts of hotkeys for almost any scenario, but it seems to me as if this specific action lacks a hotkey

Thanks a lot!

Originally Posted by Special-K

Down arrow while holding Ctrl-Shift?

Thanks.
I know this hotkey, but from what I can gather from the helpfile and my personal tests, this would be doing what I want, except that it *STOPS* at blank cells. So if somewhere in the column, you have blank cells, the selection would not go all the way down (or up), but only so far as a blank cell is found ....

Originally Posted by Palmetto

If by "mark" you mean highlight as in selecting cells, then . . .

Yes, sorry if that was uncleas, I meant "selecting" indeed.

Thanks for all your suggestions!

(Will have press more than once if there are blank rows in between.)

Well, that's exactly my problem. I was looking for a keyboard shortcut to select:

Originally Posted by boarders paradise

all cells from the current one to the last/first of column

...., not to the last/first non-blank cell in the column.

I would need this function for almost all my sheets, and I almost can't imagine that something that basic is not covere by a shortcut (considering some of the very exotic shortcuts there are otherwise .... )

You'll have to use VBA, something alongs the lines of

Code:

Sub Select_Cells()

    Dim lrow As Long, lcol As Long, rCell As Range
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    With ActiveSheet
        Set rCell = ActiveCell
        Range(rCell, Cells(lrow, lcol)).Select
    End With

End Sub

Which will select a range of cells begining with the activecell to the last row/column.

See attached workbook, to which the short cut Ctrl + g was assigned to the macro.

Originally Posted by Palmetto

See attached workbook, to which the short cut Ctrl + g was assigned to the macro.

Well, first of all, let me thank you ... I hope you didn't have to create the .xls especially for me ... or if you really did, than at least I hope you are experienced enough, that it was quick and easy for you ... I hope I didn't cause you too much work ...

I opened the .xls file, but I am not sure if I understand your idea. Because when I click on the button, or when I press CTRL+G, all cells are selected to the lower/right end of the sheet. That's the same as Excel's built-in shortcut CTRL+SHIFT+END, isn't it ?

I would need to stay in the same column ...

I would need to stay in the same column ...

You weren't specific about your requirements, which is why I said "along the lines of . ."

Use this revised code, which will select all the cells in the column from activecell to the last used row in the column.

Code:

Sub Select_Cells()

    Dim lrow As Long, lcol As Long, rcell As Range
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = ActiveCell.Column
    
    With ActiveSheet
        Set rcell = ActiveCell
        Range(rcell, Cells(lrow, lcol)).Select
        On Error Resume Next
    End With

End Sub

How would I have to change the macro for it to select all the way down to the last cell, not to the first (which your code does) ?

The code as originally given already finds the last used cell in the column.
So if cell A1000 is the last used cell in column-A and cell A25 is the active cell when you run the macro, the range selected is A25:A1000.

If A10000 is the last used cell, then it selects A25:A10000.

What do you mean by "down to the last cell"?

When I copy your code exactly as is, the macro selects "up", not "down".
So for example, ...

Originally Posted by Palmetto

So if cell A1000 is the last used cell in column-A and cell A25 is the active cell when you run the macro, the range selected is ...

A1:A25 for me (and not A25:1000 like you wrote).

Which is not bad anyway, because I need 2 macros for both directions "up to first cell" and "down to last cell" (in column). So your macro already does "up to first cell" for me .... hence my efforts to modify your code so that it would select downwards from the active cell to the last cell in the column.

But the modifications I tried (and posted above) resulted in errors (obviously, because I have no clue of VisualBasic ...)

Hm ....
I don't know if you will believe me, but I swear I didn't change anything, but now, your macro "selects down to the last cell", not up to the first cell ...

Well, anyway, I would need both macros, so how would I have to change the 3rd line of your code to get the other direction ?