Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Keystroke for Autofill? 5
- Thread starter kwill
- Start date
-
3
- #2
- Thread starter
- #3
-
1
- #4
Just want to add that as long as there is data alongside ALL of the cells you're copying a formula to, just double-click the fill handle to have it auto-fill all the way down. Anne Troy
TortolaChris
Technical User
When I try this I am only managing to fill down - rather than auto fill down. I'm using 2000 is this the problem, is there something I ought to change in options or is there some potential for operator hamfistedness that I could be guilty of?
I was only looking for a solution to this question last week after arguing with a mouse junkie about the merits of keystrokes so would be particularly grateful for help.
Thanks!
I was only looking for a solution to this question last week after arguing with a mouse junkie about the merits of keystrokes so would be particularly grateful for help.
Thanks!
VulcanJedi
Technical User
Darn you need a macro for all them keystrokes !! 
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!
-
1
- #9
DaleWatson123321
Programmer
For those interested in setting up a keyboard shortcuts that activate macros for AutoFilling_Down and AutoFilling_Across, here are the steps...
OBJECTIVE: To create two separate keyboard-shortcuts to enable you to "AutoFill-Down", and to "AutoFill-Across"
You will be able to activate AutoFill_Down by holding down the <Alt> key and hitting the comma ( , )
You will be able to activate AutoFill_Across by holding down the <Alt> key and hitting the period ( . )
These will work with Numbers, Dates, or Text with a number following the text (e.g. Part001)
Note: As with all "AutoFills", you only have to enter the first two cells. Next you highlight the range you wish to AutoFill, including the first two cells. Then use the related keyboard shortcut - for AutoFilling Across or Down.
STEPS to create the above keyboard shortcuts:
1) Open (or unhide) your PERSONAL.xls file. If by chance you don't have a PERSONAL.xls file, create one by saving a workbook under: D:\Program Files\Microsoft Office\Office\xlstart\PERSONAL.XLS
2) Open the Visual Basic Editor - using the menu: Tools - Macro - Visual Basic Editor, or <ALT> <F11>
3) Copy and Paste the following routines into an existing Module. If, by chance you don't have a Module created, use the VB menu: Insert - Module.
Sub AutoFill_Down()
Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill
End Sub
Sub AutoFill_Across()
Selection.DataSeries Rowcol:=xlRows, Type:=xlAutoFill
End Sub
4) Right-click on the Toolbar section at the top of your screen, and choose "Customize" at the bottom of the dropdown menu.
5) Click on the "Commands" tab.
6) Under "Categories" (on the left) scroll down and select "Macros"
7) On the right-side (under Commands), click-and-drag the " Custom Button" to one of your existing toolbars. Normally, you would place it on the right-side of the existing toolbar.
8) Option: If you prefer, you can add a "new" toolbar - by clicking on the "Toolbars" tab (to the left of "Commands" - see step 5), and select "New" (on the right-side). After creating the new toolbar, drag it to the top, together with the other toolbars.
9) Now back to the "" Custom Button you inserted... While still in "Customize" (re-do Step 4 if needed), right-click on the icon.
10) On the dropdown menu, click inside the "Name" box (3rd from top). Leave the first character (the "&" but delete "Custom Button". After the "&", type: .FD (FD is short for Fill Down) ...the "&" dictates that the next letter following (in this case the "." can be used in conjunction with the <Alt> key to activate the icon. This can be done with almost ALL of the regular menu functions (setting up separate icons to perform regular functions such as setting Column-Width or Row-Height). But in this particular case, we will be attaching a "macro" to this icon.
11) After typing the name in Step 10, click on "Text Only (Always)" - further down on the same dropdown menu.
12) While still in "Customize", Right-Click on the same icon (now ",FD", and choose "Assign Macro" (bottom of dropdown).
13) From the "Assign Macro" window, double-click on the "AutoFill_Down" macro, and then Close.
14) Repeat Steps 4-13 for the "AutoFill_Across" macro. But, in Step 10, use the name ".FA", and in Step 13, assign the "AutoFill_Across" macro.
Note: If you prefer, you can use longer names, but of course they will take up more room on your toolbar. You might initially decide to use longer names, but then shorten them later once you're familiar with the shortcuts, and perhaps want to add other shortcuts that require more room.
IMPORTANT: Be sure to save your PERSONAL.xls file before leaving Excel.
I hope this helps. Keyboard shortcuts indeed can save a CONSIDERABLE amount of time (over using the mouse), so I hope this will encourage the creation of additional keyboard shortcuts - particularly for often-used steps such as setting Column-Widths and Row-Heights. I personally use <Alt> W for Column-Width, and <Alt> H for Row-Height. This requires eliminating the "&" character from "Window" and from "Help" (on the main menu). I use Window and Help infrequently, so I consider I can use <Alt> <Tab> to go to another Window, or hit <F1> for Help, or perhaps "dust off the mouse".
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
OBJECTIVE: To create two separate keyboard-shortcuts to enable you to "AutoFill-Down", and to "AutoFill-Across"
You will be able to activate AutoFill_Down by holding down the <Alt> key and hitting the comma ( , )
You will be able to activate AutoFill_Across by holding down the <Alt> key and hitting the period ( . )
These will work with Numbers, Dates, or Text with a number following the text (e.g. Part001)
Note: As with all "AutoFills", you only have to enter the first two cells. Next you highlight the range you wish to AutoFill, including the first two cells. Then use the related keyboard shortcut - for AutoFilling Across or Down.
STEPS to create the above keyboard shortcuts:
1) Open (or unhide) your PERSONAL.xls file. If by chance you don't have a PERSONAL.xls file, create one by saving a workbook under: D:\Program Files\Microsoft Office\Office\xlstart\PERSONAL.XLS
2) Open the Visual Basic Editor - using the menu: Tools - Macro - Visual Basic Editor, or <ALT> <F11>
3) Copy and Paste the following routines into an existing Module. If, by chance you don't have a Module created, use the VB menu: Insert - Module.
Sub AutoFill_Down()
Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill
End Sub
Sub AutoFill_Across()
Selection.DataSeries Rowcol:=xlRows, Type:=xlAutoFill
End Sub
4) Right-click on the Toolbar section at the top of your screen, and choose "Customize" at the bottom of the dropdown menu.
5) Click on the "Commands" tab.
6) Under "Categories" (on the left) scroll down and select "Macros"
7) On the right-side (under Commands), click-and-drag the "
Custom Button" to one of your existing toolbars. Normally, you would place it on the right-side of the existing toolbar.8) Option: If you prefer, you can add a "new" toolbar - by clicking on the "Toolbars" tab (to the left of "Commands" - see step 5), and select "New" (on the right-side). After creating the new toolbar, drag it to the top, together with the other toolbars.
9) Now back to the "
" Custom Button you inserted... While still in "Customize" (re-do Step 4 if needed), right-click on the icon.10) On the dropdown menu, click inside the "Name" box (3rd from top). Leave the first character (the "&"
but delete "Custom Button". After the "&", type: .FD (FD is short for Fill Down) ...the "&" dictates that the next letter following (in this case the "." can be used in conjunction with the <Alt> key to activate the icon. This can be done with almost ALL of the regular menu functions (setting up separate icons to perform regular functions such as setting Column-Width or Row-Height). But in this particular case, we will be attaching a "macro" to this icon.11) After typing the name in Step 10, click on "Text Only (Always)" - further down on the same dropdown menu.
12) While still in "Customize", Right-Click on the same icon (now ",FD"
, and choose "Assign Macro" (bottom of dropdown).13) From the "Assign Macro" window, double-click on the "AutoFill_Down" macro, and then Close.
14) Repeat Steps 4-13 for the "AutoFill_Across" macro. But, in Step 10, use the name ".FA", and in Step 13, assign the "AutoFill_Across" macro.
Note: If you prefer, you can use longer names, but of course they will take up more room on your toolbar. You might initially decide to use longer names, but then shorten them later once you're familiar with the shortcuts, and perhaps want to add other shortcuts that require more room.
IMPORTANT: Be sure to save your PERSONAL.xls file before leaving Excel.
I hope this helps.
Keyboard shortcuts indeed can save a CONSIDERABLE amount of time (over using the mouse), so I hope this will encourage the creation of additional keyboard shortcuts - particularly for often-used steps such as setting Column-Widths and Row-Heights. I personally use <Alt> W for Column-Width, and <Alt> H for Row-Height. This requires eliminating the "&" character from "Window" and from "Help" (on the main menu). I use Window and Help infrequently, so I consider I can use <Alt> <Tab> to go to another Window, or hit <F1> for Help, or perhaps "dust off the mouse".Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DaleWatson123321
Programmer
Minor correction - re Step #10...
After the "&", type: .FD (FD is short for Fill Down)
The above should be: ,FD (NOT .FD)
After the "&", type: .FD (FD is short for Fill Down)
The above should be: ,FD (NOT .FD)
- Status
