Noncontiguous Selection
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:
John Walkenbach:
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.
11 November 2005, 4:43 pmDick Kusleika:
I still don't know how to work it. I can do two cells with Shift+F8, but can I do three?
12 November 2005, 12:39 amKushagra Sheth:
Keep on doing shift+f8, it works.
12 November 2005, 2:01 amPiotr Świerszcz:
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
12 November 2005, 3:18 amCharlene Wright:
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
12 November 2005, 4:45 amDick Kusleika:
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?
12 November 2005, 11:34 amDick Kusleika:
Nevermind. Piotr sorted it out for me. I have to do F8 then Shift F8. This is cool.
12 November 2005, 11:36 amjkpieterse:
I like this method over the mouse+control+dragging, because you don't loose your painfully selected multiple ranges that easy.
14 November 2005, 3:04 amRay:
You could write a book with all these "secret" keyboard (i.e., mouseless) tricks! Love this new addition to my arcania of keyboard sequences.
15 November 2005, 11:45 pmRay:
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.
15 November 2005, 11:56 pmCharlene Wright:
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
18 November 2005, 3:03 amJan:
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
21 November 2005, 4:44 amLumpy:
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?
29 October 2007, 12:40 pmJon Peltier:
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.
30 October 2007, 6:01 amDuftopia:
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
3 December 2007, 10:59 amDuftopia:
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!
3 December 2007, 11:09 amHoward Dore:
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.
4 December 2007, 3:48 amJon Peltier:
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.
4 December 2007, 7:49 am