If you began using Excel after Excel 95, you probably are not aware that there once was a toolbar button called 'Center Across Selection' that allowed you to center text from one column across a range of columns without requiring the cells to be merged. This was a very useful tool, especially for centering report headings. But for some reason, beginning with Excel 97, Microsoft saw fit to replace this tool with a 'Merge and Center' button.
On the surface they both seem to perform the same function but, if you have ever used Merge and Center, you have probably discovered that merged cells can often be troublesome. Merged cells can often interfere with inserting or deleting cells, pasting, selecting, sorting, etc...
Oh sure, you can still access the 'Center Across Selection' command by pressing CTRL+1, selecting the Alignment tab and, from the Horizontal alignment dropdown, selecting 'Center Across Selection' . But adding a one-click button on your Ribbon or toolbar will make this task much easier.
This week I am going to show you how to create a simple macro to bring back that long-lost, and very useful, 'Center Across Selection' tool to Excel.
Step 1: Record a CenterAcrossSelection macro in your Personal Macro Workbook
1) Select three cells on a single row on a worksheet;
2) Click the Record Macro button on the left end of the Status Bar (next to Ready).
| If you don't see the Record Macro
button... For Excel 2010-2013, click the File tab then select Options. On the left hand side of the Excel options dialog box, click Customize Ribbon. Then, on the right hand side box select Developer and click OK. For Excel 2007: click the Office button then Excel Options. From the Popular category on the left, select Show Developer Tab in Ribbon from the Top Options for Working with Excel section. |
2) In the Record Macro dialog, type 'CenterAcrossSelection' (no spaces) in the macro name field. From the 'Store Macro In' dropdown, select Personal Macro Workbook. Optionally, if in addition to a button on your Quick Access Toolbar, you would like to be able to run the CenterAcrossSelection macro using a keyboard shortcut, click in the 'Shortcut key' field and press the Shift key and the C key. You'll see Ctrl+Shift+C displayed (You can use a different letter than C if you already are using CTRL+SHIFT+C for something else) . Click OK;
3) Follow this step EXACTLY: From the Home tab, click the small 'dialog launcher' arrow on the bottom right corner of the Alignment group. On the Alignment tab in the Format Cells dialog, click the Horizontal dropdown and select Center Across Selection. Finally click OK to close the dialog;
4) Click the Stop Recording button on the StatusBar. Excel has just created a macro in your Personal Macro Workbook;
Although 'recording' a macro is an easy way to write VBA code, the macro recorder usually writes way more code than is necessary. For example, it often includes coding for all default values related to the macro recorded. Since the default values will be applied anyway, it isn't necessary to include them in your VBA code. Therefore, it is advisable to edit the code generated by the macro recorder and remove those redundant bits of code.
1) To open the Visual Basic Editor window, press ALT+F11;
If you don't see your CenterAcrossSelection macro code on the right side of the screen, find VBAProject (PERSONAL.XLSB) in the Project Explorer tree on the left and double click it's module to display the code on the right.
Your original recoded macro should look similar to this.
2) Highlight and delete the code as shown below;
The final code will be...
Sub CenterAcrossSelection()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
End Sub
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
End Sub
7) Now that you have your VBA code recorded and edited, click File, Save and close the Visual Basic Editor window;
Step 3 Add a button to your Quick Access Toolbar and assign it to the macro
To make it super easy to run your CenterAcrossSelection macro, add a button to the QAT and assign it to your macro. Here's how...
1) Right-click on the Ribbon and choose 'Customize the Quick Access Toolbar';
2) From the 'Choose commands from' dropdown, select Macros;
3) From the list of macros select 'PERSONAL.XLSB!CenterAcrossSelection' and click the Add>> button;
4) Select CenterAcrossSelection on the right side of the dialog and click the Modify... button;
5) Look for an image to use on the QAT and click OK, then OK.
If
you want, you can edit the 'Display name' text in the 'Modify Button'
dialog. This is the text that will display in the tool tip when you
point your mouse pointer to the button on the QAT.
Step 4: Now it's time to try out your new QAT command
1) Select the cell you want to center across several columns and drag to the right to highlight the columns you want to center across;
2) Click your new 'Center Across Selection' button on the Quick Access Toolbar. Your text will appear to be centered across the selected columns however the text remains in the original cell on the left.
|
Would you believe me if I told
you that
you can DOUBLE your productivity in Excel? How about TRIPLE it ? or QUADRUPLE it ? or MORE ? If I showed you a tip where you could do a task in Excel in five seconds that would typically take you 20 minutes to an hour or more, what would you call that? Productivity on steroids? ...and that's just ONE tip! "Give me 10 or 15 minutes each week and I will show you how learning a few of my time-saving 'Spreadsheet Tips From An Excel Addict' on a regular basis will seem almost effortless BUT will quickly help you BOOST your productivity in Excel beyond your imagination" - Francis Hayes, The Excel Addict Get my FREE Twice-Weekly
Newsletter
"Spreadsheet Tips From An Excel Addict" Now being read by more than 37,000+ Excel Addicts all over the world |
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.