Annoyingly, there’s a shortcut for Merge & Center on the Ribbon, but nothing for Center Across Selection which is far superior.
Center Across Selection achieves the same result but doesn’t cause the rude interruptions to selecting ranges that Merge & Center does.
To remedy this, I consulted ChatGPT to write a Center Across Selection shortcut macro that I can attach to an icon and place on my Quick Access Toolbar, and I’m happy to say it did a pretty good job.
Watch the Video
Download the PDF and Macro Code
Get step by step instructions here in a handy PDF.
Get the text file with Center Across Selection macro code.
Enter your email address below to download the files.
ChatGPT Prompt and Response
It’s important that you write your prompt as clearly and with as much information as possible.
The more detailed and precise you can be, the better the result. Although, there’s no guarantee it’ll do everything you ask, as you’ll see.
Below is my prompt:
Write a VBA macro that will remove merged cell format from the selected cells and replace it with center across selection alignment. Make it so I can assign the macro to a button that I put on the Quick Access Toolbar.
The response was this code, nicely commented so I could follow what each section was doing:
It also gave me some instructions on what to do with the code:
Corrections to ChatGPT Code
Unfortunately, ChatGPT assumed I wanted to remove all formatting. I didn’t. And by putting the macro in the worksheet (step 2 above) I will only be able to use it in the current file, not any file I have open.
In the image below you can see where it has assumed I want to clear formatting and has added a line of code to do so: clear.CellFormats
All I need to do is remove the sections in orange boxes and I’m good to go.
The assumption that I wanted to put the macro in the current workbook is an easy fix…if you know how.
I asked ChatGPT several times and it eventually told me to save it in a module in my Personal Macro Workbook.
Inserting Center Across Selection Shortcut Code in Excel
To make the Center Across Selection shortcut macro available in any workbook you open, you can save it in your personal macro workbook.
The personal macro workbook is a hidden workbook that opens automatically whenever you start Excel and is available in all workbooks.
Here's how you can save the macro in your personal macro workbook:
- Press `Alt+F11` to open the Visual Basic Editor in Excel.
- In the Project Explorer pane, right-click on the VBAProject (PERSONAL.XLSB) and select Insert > Module. This will insert a new module in your personal macro workbook.
If you don’t see your personal macro workbook, check out this post: Easy way to create a Personal Macro Workbook.
- In the code window of the new module, paste the VBA code for the macro.
- Save and close the Visual Basic Editor.
Assigning the Macro to an Icon on the QAT
To add the macro to the Quick Access Toolbar:
- Right-click on the Quick Access Toolbar and choose "Customize Quick Access Toolbar".
- In the Excel Options window, under the "Choose commands from" drop-down list, select "Macros."
- In the left pane, select "PERSONAL.XLSB!RemoveMergeFormatAndApplyAlignment" (replace "RemoveMergeFormatAndApplyAlignment" with the actual name of your macro if you’ve changed it).
- Click the "Add" button to add it to the Quick Access Toolbar.
Bonus tip: if you prefer a different icon, click ‘Modify’ and choose from the list.
- Click "OK" to close the Excel Options window.
See the Macro in Action
Other Excel ChatGPT Automations
Now you see how easily you can create shortcuts like this, please share your ideas in the comments for other macros you might like ChatGPT to write for you.