The Control+ArrowKey shortcut is my new favorite shortcut. I seem to be using the heck out of it lately.
In Excel (cell navigation), it works like the End key. Control+Down Arrow is the same as End, Down. Add the Shift key for a handy way to select column of data.
In Excel (cell editing), the left and right arrows move to the next word. Find a long formula, press F2 to edit it, and start pressing Control+Left Arrow to move left one “word” at a time. Words are not quite so easy to distinguish as in a text editor, but it does a pretty good job. The up and down arrows don’t seem to do anything special. At least not that I can see.
In the VBE, left and right arrows move one word at a time, just like in cell editing. Since the Object.Property.Property type strings have a lot of periods, they make for nice breakpoints. The up and down arrows move one procedure at a time. I used to use Control+PgUp/PgDn, but I like this better. Instead of taking you to the first line of the procedure, which is usually a Sub or a procedure header, this takes you to the first line below the procedure declaration statement (Sub, Function, etc.). Also unlike PgUp/PgDn, this scrolls the cursor to the top of the code window – very handy.
DK,
I like the transition navigation keys checked in the Tools-Options-Transition panel. It allows the use of existing blocks of data to control cell selections. However, the Ctrl+arrow right&left is reduced to a Tab function.
Brett
Control + Arrow in the VBE is new to me. Very nice!
Since working with big data lists, ++ then ++ to select the whole list has been invaluable. Lost of folks want to know how that is done when they see it.
Opps – angle brackets don’t show well in the comments. This should read:
Since working with big data lists, CTL + Arrow Down then CTL+Arrow Right to select the whole list has been invaluable. Lost of folks want to know how that is done when they see it.
The CTRL + ARROWs as well as CTRL + SHIFT + * (select current region) have been invaluable in my work in Excel itself. However, I had limited those to Excel, now I can extend it to VBE. Thanks for the tip.
Control-arrow works Windows-wide when editing text. Only the specific delimiters that make the movement stop differs from application to application.
This is one of the reasons why the left control key on my keyboard is the shiny one: It is extensively used.
Another very useful key combo in the VBE: control-spacebar. Dunno how I have ever lived without it… Just type the first couple of letters of a variable name and hit the shortcut…..
I don’t have any shiny keys on my keyboard…does that mean I should be working harder??
My favorites are also the CTRL + arrows and the CTRL + SHFT + * to select the current region. I’m also a big fan of using CTRL + PGUP AND CTRL + PGDOWN to navigate between worksheets. I’m not a mouse-o-phobe like some, but I do like to save movement and energy and these shortcuts are great for us lazy, I mean progressive, thinkers.
One thing I’ve noticed in Excel 2003 is that CTRL + A no longer selects the entire sheet by default, but also seems to select the current region, unless the cell pointer is in an empty cell or there appears to be no distinguishable region. Doing CRTL + A twice selects the sheet. Is there a way to get around this?
It’s interesting to look at my keyboard. It’s about two years old, and I don’t think I’ve ever cleaned the keys. The left Ctrl button is very shiny, and so is Shift, Alt, and Tab. The others are filthy.
After I typed that, I noticed a key that I’d never noticed before. It’s on the right side, between the Alt and Ctrl keys (keys that I never use). Press, it and you get a shortcut menu for the selected item! I know that there’s a key combination that shows the shortcut menu, but I can never remember it. But I can probably remember that key.
I’m using one of those Microsoft media keyboards (or whatever it’s called). The one with the ergonomic split that puts the keys at an angle and all of the extra buttons on top that I never remember to use.
> I know that there’s a key combination that shows the shortcut menu
Shift + F10. And I use that shortcut key all the time. I have the MS Ergonomic 4000 and I use almost every key on it, probably the two that I never use are PrtScrn and ScrLk. The rest get a fair amount of pushing.
Juan Pablo,
Thank you!! Shift + F10 just became a new friend. I don’t hate the mouse, but I sure begrudge the time spent moving away from the keyboard.
Brett
What century are you guys living in ?
Next thing you know you will be telling me that you can’t use the mouse scroll wheel in vba.
(gasanov)
Most of these shortcuts have been around since Windows 3.0
My favourite Excel shortcut is Ctrl + Shift + A
Try it type “=PMT(“
Then before you think to yourself what were those parameters?
hit the Ctrl Shift A and all is revealed.
(I know that we’re really talking shortcuts, but…) I prefer, instead of Jan’s Ctrl + Shift+ A while entering a formula, to select the equals sign in the formula bar – giving a template for entering the parameters as well as basic help descriptions. (Of course, both techniques work for custom functions as well as built-ins).
Two more you might like….
Ctrl + F3 for a find using the current word (and then F3 repeatedly). Great for those who hate the big stupid find / replace box.
Shift + F2 for jumping to the declaration or Code module, the best for reviewing someones code.
Ctrl + F3 is also a great way to define names in excel , but Ctrl + Shift + F3 is even better.
Ok it was 4 (Without excel I can’t do mental arithmetic.)
One more easy bunch to remember are the Ctrl shift 1 through 0
Its easy to remember because you can see some hints on the keyboard.
! = Basic number
$ = money
% = Percent
^ = scientific
&= Wrap a border
@ = Time
# = Date
~ = General
1= show me my formatting options I screwed up the keyboard shortcuts….
They are really handy for formatting.
Re: Scroll wheel in VBA
I installed the beta version of Excel 2007 but had to remove it because of Q/A testing on the box I’m using, so I’m back to using Excel 2003 on Windows XP. In the VBA editor I discovered my mouse scroll wheel worked! So for those who have Excel 2007 installed, or who have installed it then uninstalled it, your scroll wheel should still work…at least in Excel 2003 :-)
Speaking of shortcuts, does anyone have custom shortcuts for those commands that you can’t otherwise do wtih the keyboard? For example, here’s two modules to allow you to expand and contract an outline one level at a time, similar to clicking on the little number boxes on the upper left hand side. I know you can use Data->Group and Outline->Hide Detail / Show Detail for any selection, but there’s no keyboard way to do it for the entire level without VBA.
Dim intMax As Integer
Dim lngRows As Long
Dim lngCount As Long
lngRows = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
For lngCount = 1 To lngRows
If Not Rows(lngCount).Hidden Then
If Rows(lngCount).OutlineLevel GREATERTHAN intMax Then intMax = Rows(lngCount).OutlineLevel
End If
Next
On Error Resume Next
ActiveSheet.Outline.ShowLevels (intMax + 1)
End Sub
Sub ContractOutline()
Dim intMax As Integer
Dim lngRows As Long
Dim lngCount As Long
lngRows = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
For lngCount = 1 To lngRows
If Not Rows(lngCount).Hidden Then
If Rows(lngCount).OutlineLevel GREATERTHAN intMax Then intMax = Rows(lngCount).OutlineLevel
End If
Next
On Error Resume Next
ActiveSheet.Outline.ShowLevels (intMax – 1)
End Sub
You’ll have to replace GREATERTHAN with a > sign.
I’d also love a way to duplicate the autofill function you get when you doubleclick the crosshairs in the bottom right corner of the activecell. I know you can do it with a sequence of keystrokes, but I’d like an elegant way to do it with one command. I’m sure there’s others that us keyboard loving mouse haters would like to see. Moving my thumb to the touch pad just below the space bar is just too much work.
You can always use the Alt DGH etc.
They work a dream for me although I am hoping I will not have to re learn them when I get to 2007.
Other favourites are Alt TUD and Alt TUP and Alt TUA.
They are all in the menus but you get used to them pretty quick and you don’t even notice what you are pressing.
shortcut for double clicking the crosshairs in the bottom right corner of the activecell
this could be the equivalent of Edit > Fill > Down
in which case the shortcut is Ctrl + D
How do I control arrow to move to skip blank cells on a tablet with no up/down arrows?