To select cells that aren’t next to each other with your mouse, hold down the Control key while you select the cells. You can then, for instance, get the sum of those cells from the status bar. Can you do the same thing with just the keyboard? I can’t figure out how, so I wrote a procedure to do it. The F8 key will toggle EXT mode which allows you to select a range with just the arrow keys, but I couldn’t figure out how to select a noncontiguous range.
Assume you have a worksheet like this:
and you want to know what B2+B6 equals. If you were a mouse kind of a person, you might click B2, then with the Control key depressed, click B6 and read the sum from the status bar. If you’re a psycho-keyboarder, like me, you’d add this to your Personal.xls:
Public bExtendMode As Boolean
Sub ToggleExtendMode()
‘cntl+shift+e
Const sMODE As String = “Extendo-Mode: Use Control+Shift+Q to include the selection”
If bExtendMode Then
Application.StatusBar = False
bExtendMode = False
Set grExtend = Nothing
Else
If TypeName(Selection) = “Range” Then
Application.StatusBar = sMODE
bExtendMode = True
Set grExtend = Selection
End If
End If
End Sub
Sub AddToExtend()
‘cntl+shift+q
If TypeName(Selection) = “Range” And bExtendMode Then
Set grExtend = Union(grExtend, Selection)
grExtend.Select
End If
End Sub
With Control+Shift+E, you can turn on Extendo-Mode:
With Extendo-Mode on, you can add selections to the current selection simply by selecting them and pressing Control+Shift+Q (I couldn’t think of a good letter for that one).
| Start in B2 and press Cntl+Shft+E. B2 is added to the ExtendoRange | Arrow down to B6 | Press Cntl+Shft+Q and the selection become B2, B6 |
 |
 |
 |
It would be nice if I could have a visual indicator of which cells were included before more were added, but it seems like a lot of work for not that much benefit. As always, your comments are welcome. Oh yeah, and here’s the answer:
I hate to be the one to break it to Dick, but the secret is to press Shift+F8 while using the keyboard method. That will allow you to make a multiple selection using only the keyboard.
But I do like the term Extendo-Mode.
I still don’t know how to work it. I can do two cells with Shift+F8, but can I do three?
Keep on doing shift+f8, it works.
Yes, you can.
Recipe:
1. Go to F17 and press Shift+F8
2. Go to H19 and press F8 and next press SHIFT+F8
3. Go to J14 and press F8 and next press SHIFT+F8
4. Go to …
Any easier way ?
Keep your blog going, fantastic work.
Piotr
Hi Dick,
Yes. From a corner of the first area, press F8 to get started, use the arrow keys to select the first area. Press Shift-F8 to lock it in.
Move to one corner of the next area, press F8 to start a new area, arrows to select area, Shift-F8 to lock it in.
Repeat until all desired areas are selected.
Charlene
Thanks Charlene. Can you explain how I would select A1, A3, and A5? I can follow your directions if my areas are multicell, but it doesn’t seem to work for single cell. What am I missing?
Nevermind. Piotr sorted it out for me. I have to do F8 then Shift F8. This is cool.
I like this method over the mouse+control+dragging, because you don’t loose your painfully selected multiple ranges that easy.
You could write a book with all these “secret” keyboard (i.e., mouseless) tricks! Love this new addition to my arcania of keyboard sequences.
By the way, is there a name for that SUM (which can also be AVERAGE, etc) indicator at the bottom of screen? I’ve always wanted to know what the folks at Excel call that.
Hi Ray-
Per http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B130490 :
AUTOCALCULATE
You no longer need to use a calculator or enter temporary formulas on a worksheet when you want to quickly check a total. Now you can just select the range you want to sum, and the answer will appear in the status bar at the bottom of the screen. You can also average the selected numbers or count the entries by clicking the AutoCalculate area in the status bar with the right mouse button.
-Charlene
Not bothered about its name but did you note that there are going to be 6 “AUTOCALCULATE” on the bottom of the screen for Excel 12. Seems like a boring name for such a useful function… I would have prefered something more like Extendo-Auto-Calculato-Mode if it were up to me.
http://blogs.msdn.com/excel/archive/2005/11/18.aspx
Is there a programmatic way to copy discontinuous ranges?
For example:
Worksheet(“Sheet2?).Range(“A1:C3?).Value = _
Worksheet(“Sheet1?).Range(“A1:A3,C1:C3,E1:E3?).Value
Highlighting cells with the Ctl key and using Copy+Paste is swell but can it be done entirely within VB? I keep running into problems copying this way, specifically the first row of my “copy from” sheet is duplicated on each row of my “copy to” sheet.
Can Excel only resolve continous regions even if the region size is exactly the same? Does everything have to be one-to-one?
Lumpy –
The range does not have to have equal sized areas, but it must be shapes as if it is a larger rrectangle that is intersected by complete rows and columns which separate the areas of the range.
You could use any of the following:
“A1:A3,C1:C3,E1:E3?
“A1:A3,C1:D3,L1:Q3?
“A1:A3,A5:A7,C1:C3,C5:C7,E1:J3,E5:J7?
The discontiguous range can be shown in compact form by selectively hiding rows and columns.
I am running Excel 2003, now with excel 2007 has this bug been fixed!.
Honestly I came from the Lotus 123 days and one thing I can say about the Lotus people, even the quatro and aseasy 123 people – they fixed there bugs.
Sometimes CTRL-Select works, sometimes it doesn’t what type of logic is this, I really like to know what is behind the scenes on this program issue?, is it because Microsoft when in doubt uses the random number generator to determine if the function should do what it was designed to do when the program fails!
You see I understand some MS issues like sorting numbers and Alpha in the DIR (dos based) environment that they did not fix in the windows versions that followed, I have clients who can never get use to the problem where number 20 is sorted before 3, they could have at the very least added a switch to make windows act like it knows numbers, but I know deep down inside where the problem lies and even I can’t find an easy effective fix.
But this absolutely silly, time wasting issue that follows us from version to version of excel just does not get addressed, and people have dozens and dozens of macros to circumvent the problem.
The Excel GURU John Walkenbach suggest the F8 trick, what does that tell you?, even a genius who probably has a repore with Microsoft has to concoct an “extra Keystroke” slower method to get around such an apparently simply issue.
Someone tell me for the sake of my sanity why MS can’t fix this “multiple selection” issue, so that maybe I will go back to hacking and write a patch to fix it (and remove excel’s license check as well) – you see I’ve got some more serious array issues to discus after I fix this one.
Duf
Well the F8- Shift f8 doesn’t seem to work for me either, its simple:
I need to select one entire row, page down to find another item i want select it and do this for several pages then copy paste it.
I still get cannot copy multiple selections, well its going to be a long day!
Is your issue that use of the ‘Page Down’ key stops multiple selection copy from working? As far as I can see, if you page down using the scroll bar it works OK but if you use the Page Down key it doesn’t! How bizarre is that? I’m on Excel 2002 by the way.
You can select multiple ranges, no problem, using CTRL+Click. In order to use this range for anything useful (i.e., to copy or to use as chart source data, etc.), it must be a well-defined discontiguous range. Than means, it must be definable as a large rectangle, cut through by complete rows and columns. So that if the unselected rows and columns are removed (or hidden) and the range is pushed together, the result is a single rectangle. No cells in the multiple selection can be selected twice. Also, if one sub rectangle is A1:B2 and you want to add A4:B5, you have to do it in one step, A4:B5, not in two steps, A4:B4 plus A5:B5. When you follow these guidelines, you should be more successful with discontinguous ranges.
hello, hope someone can help, am trying to copy non-contigous cells from one worksheet and paste them in the EXACT SAME cells in another worksheet, cant seem to work out how to do this, as excel just pastes them one under another…..
@Dave S,
You didn’t give us any specifics, so you will have to modify this general approach. Assuming your non-contiguous cells are the Selection (replace Selection with a reference to your actual non-contiguous range), give code structured like this a try…
Dim A As Range, Cell As Range
For Each A In Selection.Areas
A.Copy Worksheets(“YourOtherSheet”).Cells(A(1).Row, A(1).Column)
Next
@ Rick R
Thank you very much, but i am a real Excel novice, so maybe this is not the correct page for me, apologies if its not, I dont know VBA or any of that stuff, just want a keystroke that lets me copy with holding CTRL down, cell A1, A3 and say A7,,,,those are just examples, to a diff SHEET (sheet 2 say), and pasting them in the exact same cells,,,,if I do it, those three paste into A1, A2 and A3, not from where I copied them…..I just want to know if this is possible with some sort of special paste….thank you for your previous help.
@David Silks,
To the best of my knowledge, there is no way to do what you want without using some kind of VB code. If you tell us whether the source will always be user selected, or is it always a specific set of cells, and whether the copy will always be placed on Sheet2, or if the sheet is to be determined at the time of the copying, then we can design the code for you and tell you how to install it in your workbook.
@Rick R
Thanx Rick, the source is always user selected and can change. The copy could be any other sheet in the workbook, not always sheet 2. Sheet to paste on is determined at the time of paste. Thank you once again, appreciated.
@Davd Silks,
This was the best interface I could come up with, so I hope you like it. From any worksheet in your workbook, press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its Menu Bar, then copy/paste the following code into the code window that opened up…
Dim SelectedCells As String, SelectedSheet As String
Dim CopyToSheet As Worksheet, A As Range
Const Message = “Select any cell on the worksheet you “ & _
“want to copy the selected cells to…”
SelectedSheet = ActiveSheet.Name
SelectedCells = Selection.Address
Set CopyToSheet = Application.InputBox(Message, _
“Select Copy To Sheet”, Type:=8).Parent
For Each A In Worksheets(SelectedSheet).Range(SelectedCells).Areas
A.Copy CopyToSheet.Range(A.Address)
Next
End Sub
That’s it. Go back to any sheet with data that you want to copy, select the cells you want to copy (this selection can be any combination of contiguous and non-contiguous ranges from any columns. rows or rectangular ranges of rows and columns) and press ALT+F8 to bring up the macro dialog box. Select CopyToSameAddress from the list and click the Run button. A dialog box will appear asking you to go to the worksheet you want to copy the cells to and select any cell on that worksheet and click the OK button. Your cells were just copied to the sheet whose cell you selected in response to the dialog box question.