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.
Table of Contents
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.
Now the macro will be saved in your personal macro workbook and will be available in any workbook you open.
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.
Now the macro button will be available on the Quick Access Toolbar in any workbook you open.
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.
There is a Center-Across command available, albeit a couple of clicks down. Use the expansion of the Alignment group in the ribbon and the resulting dialog box offers Center Across Selection as well as several other options for horizontal alignment.
Yep, it’s the same one, Alan. I probably should have mentioned that 😉
Hi Mynda et al,
I’ve had this macro on a keyboard shortcut (ctrl-sh-A) for many years:
Sub CAS()
With Selection
.MergeCells = False
If .HorizontalAlignment = 7 Then .HorizontalAlignment = 1 Else .HorizontalAlignment = 7
End With
End Sub
Removes any merge formatting, then toggles between Centre-Across-Selection and General alignments – Simples!
The only downside is that there’s no Undo
Nice! Thanks for sharing, Jim.
I’ve consulted MS AI Chat about two Macro1() and Macro2() to add up values from one range onto another. Macro1() which is a recored Macro run as expected, but Macro2() got a type mismatch error. AI Chat gave me several suggestions with detaied explanations but none is workable. I’ve chatted with AI Chat many times and that is the only one not successful. I would like to provide the conversations as below for your reference if it would not bother you too much.
Sub Macro1()
Range(“G3:G5”).Select
Selection.Copy
Range(“I3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Sub Macro2()
Range(“I3:I5”).Value = Range(“I3:I5”).Value + Range(“G3:G5”).Value
End Sub
The reason why Macro2 is not working is that it is trying to add two ranges of cells that are not of the same size. The range “I3:I5” has 3 cells while the range “G3:G5” has 3 cells as well. However, the “+” operator can only be used to add two numbers or two ranges of the same size. Therefore, you need to change the code in Macro2 to:
Sub Macro2()
Dim i As Integer
For i = 3 To 5
Cells(i, 9).Value = Cells(i, 9).Value + Cells(i, 7).Value
Next i
End Sub
This code will loop through each cell in column G and add its value to the corresponding cell in column I. This will fix the type mismatch error you are seeing.
You can use the Resize method to make the two ranges the same size before adding them together without using a for-next loop.
This code will resize the range “I3:I5” to be the same size as “G3:G5” and then add them together using the “+” operator. This will work even if the two ranges have thousands of cells.
Sub Macro2()
Dim range1 As Range
Dim range2 As Range
Dim result As Range
Set range1 = Range(“I3:I5”)
Set range2 = Range(“G3:G5”)
Set result = range1.Resize(range2.Rows.Count, range2.Columns.Count)
result.Value = CInt(range1.Value) + CInt(range2.Value)
End Sub
This code converts the data types of both ranges to integers using the CInt() function before adding them together. This should resolve the type mismatch error you are seeing.
Received message. The error you are seeing is a type mismatch error. It occurs when you try to perform an operation on two variables that are not of the same data type. In your case, it is likely that the two ranges have different data types. You can try converting the data types of the two ranges to match each other before adding them together. Here is an example of how you can convert the data types of the two ranges: “` Sub Macro2() Dim range1 As Range Dim range2 As Range Dim result As Range Set range1 = Range(“I3:I5”) Set range2 = Range(“G3:G5”) Set result = range1.Resize(range2.Rows.Count, range2.Columns.Count) result.Value = CInt(range1.Value) + CInt(range2.Value) End Sub “` This code converts the data types of both ranges to integers using the `CInt()` function before adding them together. This should resolve the type mismatch error you are seeing.
No, the error still occured
I’m sorry to hear that. Another reason why you might be seeing the type mismatch error is that the two ranges have different sizes. In this case, you can try resizing the two ranges to be the same size before adding them together.
Here is an example of how you can resize the two ranges:
Sub Macro2()
Dim range1 As Range
Dim range2 As Range
Dim result As Range
Set range1 = Range(“I3:I5”)
Set range2 = Range(“G3:G5”)
If range1.Rows.Count range2.Rows.Count Or range1.Columns.Count range2.Columns.Count Then
MsgBox “The two ranges have different sizes. Please resize them to be the same size.”
Exit Sub
End If
Set result = range1.Resize(range2.Rows.Count, range2.Columns.Count)
result.Value = CInt(range1.Value) + CInt(range2.Value)
End Sub
This code checks if the two ranges have different sizes using an If statement. If they do, it displays a message box asking you to resize them to be the same size. If they are the same size, it resizes them using the Resize() function before adding them together.
I’m sorry to say it’s still not workable
I’m sorry to hear that. Another reason why you might be seeing the type mismatch error is that the two ranges have different data types. For example, one range might contain text while the other range contains numbers. In this case, you can try converting the text range to a number range before adding them together.
Here is an example of how you can convert a text range to a number range:
Sub Macro2()
Dim range1 As Range
Dim range2 As Range
Dim result As Range
Set range1 = Range(“I3:I5”)
Set range2 = Range(“G3:G5”)
If range1.Rows.Count range2.Rows.Count Or range1.Columns.Count range2.Columns.Count Then
MsgBox “The two ranges have different sizes. Please resize them to be the same size.”
Exit Sub
End If
‘ Convert text range to number range
range1.Value = Evaluate(range1.Address & “*1”)
Set result = range1.Resize(range2.Rows.Count, range2.Columns.Count)
result.Value = CInt(range1.Value) + CInt(range2.Value)
End Sub
not workable again
I’m sorry to hear that. If you’re still experiencing issues with your code, you might want to consider posting your question on a forum like Stack Overflow. There are many experienced VBA developers who can help you troubleshoot your code and find a solution.
I hope this helps!
Hi Julian,
You’re welcome to post your question on our Excel forum where you can also upload a sample file and someone can help you further.
Mynda
please change selection.cells.count to countLarge.
Thanks for the tip!
Dear Mynda,
Thank you so very much for sharing your knowledge with us. Materials are extremely useful. So much blessings to you and your family! Sincerely, Anura
Our pleasure! Glad we can help.
I’m concerned that the “each cell in Selection” method would remove that cell from the merge, then apply “center across selection” to only that cell, then move on to the next. I haven’t tried ChatGPT’s macro so can’t say that’s what would happen, but it’s my thought.
I tackled this on my own years ago, and here’s the macro I built to do it. It works well for me.
Sub Unmerge_CenterAcross()
Dim Cell As Range, Addr As String
For Each Cell In Selection
If Cell.MergeArea.Address “” And Cell.Value “” And _
Cell.HorizontalAlignment xlCenterAcrossSelection Then
If Cell.HorizontalAlignment = xlHAlignCenter Then
Addr = Cell.MergeArea.Address
Cell.MergeCells = False
With Range(Addr).Rows(1)
.Select
.HorizontalAlignment = xlCenterAcrossSelection
End With
End If
End If
Next
End Sub
Thanks for sharing, Jomili!