|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
I want to know if there is an easy way to create a keyboard command for some of the things I do quite often in Excel. For example, I would like to have the command <CTRL> R to insert a new row and <CTRL> C to insert a new column. Can I do this without having to write a VBA program? Alternatively, can I use (CTRL>I for a row insertion and <ALT> I for a column insertion?
Any insight that anyone would care to provide will be greatly appreciated. Thanks.
Ron M
Thanks Paul. New apps I haven’t run into before. I had a quick look at them and, being a non-techie, it wasn’t intuitively obvious to me how they fit into Excel as the sites I looked at seemed to show their use within Windows. I think I will try recording a Macro within Excel and see if I can make that work for what I want. That is about as “techie” as I can get and still follow what is happening.
A couple of other options, Ron:
1. A programmable keyboard. There are many out there, I’m very happy with the ROCCAT Isku I got 3 years ago for $70—it’s under $50 now.
2. Instead of the excellent AutoIt or AutoHotKey, I use TypeItIn which is a simple visual app with a lot of power behind the GUI. A major advantage for me is I don’t have to remember any shortcuts, I find that onerous once it gets in the 50+ range. You can still set a KB shortcut key for any command if you wish.
Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD
Ron,
Here’s the code for inserting a column by moving the currently selected column to the left.
' +-------------------------+ +----------+
'-------------------------| ColumnInsert() |-------------| 07/19/19 |
' +-------------------------+ +----------+
'Arguments: N/A
Sub ColumnInsert()
Dim rngActiveCell As Range
Set rngActiveCell = ActiveCell
With rngActiveCell
.EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Offset(0, -1).Select 'Set selection to the initial row in the inserted column.
'Remove Offset to keep selection in the old location.
End With
End Sub 'ColumnInsert
Note: the comments in the code about where the cursor will land after the insert.
Place the code in a module of your Personal.xlsx file then use Alt+F8 to find the macro and use the Options button to assign your desired hot key combo (I chose Ctrl+Shift+I). By doing this it will be available to ALL of your Excel Workbooks.
HTH :cool:
Hi Ron
You can assign shortcut keys to any macros you create.
Your shortcut key must be a letter, and is used with the [Ctrl key.
It is best to avoid using any existing Excel shortcut keys, so best not to use [Ctrl][C] for inserting columns, as that shortcut is already used for “Copy”.
And best not to use [Ctrl][R] for inserting rows as that is already used in Excel for “fill right”.
The good news is that you can use [Ctrl][Shift][letter] for your shortcuts, and most of the letters are available to use without impacting existing shortcuts. I like using [Ctrl[Shift][Z] of course.
So this means we can use [Ctrl][Shift][C] to insert Column(s), and we can use [Ctrl][Shift][R] for inserting rows, as in:
‘using shortcut [Ctrl][Shift][C] Sub insertColumn() Selection.EntireColumn.Insert End Sub ‘using shortcut [Ctrl][Shift][R] Sub insertRow() Selection.EntireRow.Insert End Sub
I have attached a file with this vba code in.
We can tell you how to assign shortcut keys to your macros if we know what Excel version you are using, e.g. Excel2010, Excel2013, Excel2016, Excel2019 etc etc etc
zeddy
Excel Battle Angel Assistant
I fixed this one for you.
Hi Ron
1. Open the workbook you want to use with the new shortcut keys e.g. fred.xlsx
2. When the workbook has loaded, press [Alt][F11] to open the Visual Basic Editor (VBE)
3. Press [Ctrl][R] to display the VBA Project Explorer in the left-pane of the Visual Basic Editor (if the left pane is not already showing). Pressing [Ctrl][R] doesn’t hurt if the left pane is already visible.
4. in the top-left corner of this pane you should see your workbook name in brackets e.g. VBAProject(fred.xlsx), so Right-click on your workbook name and select Insert -> Module from the context menu.
5. Copy the VBA code and paste it into the right-hand pane of the VBE (“Module1” window)
6. Press [Alt][F11] to return to the Excel worksheet
7. After adding the macros via the Visual Basic Editor you now need to save the workbook as a macro-enabled filetype e.g. as Fred.xlsm
8. After saving the file, press [Alt][F8] to show the list of macros..
9. click on the insertColumn macro in the list of macros to select it, then click on the [Options…] button to display the Macro Options dialog.
10. The Shortcut key will show a blinking cursor as it waits for you to type the letter key you want to use for the shortcut you are going to assign to the highlighted macro.
11. Enter C (i.e. as a Capital letter C) and it will then show as Ctrl + Shift + [C]
12. Click the [OK] button to complete this shortcut key assignment.
13. Now highlight the second macro in the list insertRow, click the [Options..] button, and then enter R (i.e. as a Capital R) for the second shortcut key assignment
14. Now click the [Cancel] button to hide the Macro Options dialog
15. Now save the workbook again.
16. Close the workbook, then re-open the workbook
17. Above the formula bar you will see a ! SECURITY WARNING Macros have been disabled. [Enable Content]
18. Click the [Enable Content] button to enable your macros.
You should then be able to test your shortcut key assignements.
zeddy
Excel Visual Effects Head
I am headed for the Masai Mara in Kenya for two weeks of animal, and other things, photography. Hoping to catch the great migration.
Hi Ron
It is actually possible to create shortcut keys in Excel for inserting rows and inserting columns without using any macros. You can do this because Excel already has these commands available in the Top-Panel Ribbon.
To make them available via keyboard-shortcut rather than mouse-clicks, you just need to add these commands to a Quick Access Toolbar (QAT). Once they are in your QAT, you can type [Alt][1] to execute your first QAT item, [Alt][2] to execute your 2nd QAT item, [Alt][9] to execute your 9th item etc etc etc.
To see what you need to type for each item in your QAT, just press the [Alt] key to see the corresponding keys.
If you follow the link in the first reply to your post, it will tell you how to customize the Quick Access Toolbar (it applies to your Excel in Office365).
So, if you put the Insert-Columns command as your first item in your QAT, you could then just use [Alt][1] to insert columns. Putting the Insert-Rows command as your second QAT item lets you use [Alt][2] etc etc etc.
The QAT is a very useful feature and you should definitely add your favourite commands to it.
You can Export/Import QAT settings, which allows you to transfer your favourites to another computer. These are stored in files with a file extension of .exportedUI but unfortunately I cannot upload a zeddy.exportUI file to this forum.
zeddy
Excel Disruptive Innovation Team
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
