My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Center Across Selection Macro

You are here: Home / Excel VBA / Center Across Selection Macro
macro to center across selection
by

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

Subscribe YouTube

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.

By submitting your email address you agree that we can email you our Excel newsletter.

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:

VBA code from ChatGPT

It also gave me some instructions on what to do with the code:

Instructions how to use VBA code from ChatGPT

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.

Amending VBA code from ChatGPT

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:

  1. Press `Alt+F11` to open the Visual Basic Editor in Excel.
  2. 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.

  1. In the code window of the new module, paste the VBA code for the macro.
  2. Save and close the Visual Basic Editor.

Save VBA code in module

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:

  1. Right-click on the Quick Access Toolbar and choose "Customize Quick Access Toolbar".

Customize Quick Access Toolbar

  1. In the Excel Options window, under the "Choose commands from" drop-down list, select "Macros."
  2. In the left pane, select "PERSONAL.XLSB!RemoveMergeFormatAndApplyAlignment" (replace "RemoveMergeFormatAndApplyAlignment" with the actual name of your macro if you’ve changed it).
  3. 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.

  1. Click "OK" to close the Excel Options window.

Change icon for macro on Quick Access Toolbar

Now the macro button will be available on the Quick Access Toolbar in any workbook you open.

See the Macro in Action

Animated GIF of macro running

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.

macro to center across selection

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category:

Reader Interactions

Comments

  1. Alan Cannon

    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.

    Reply

  2. jim

    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

    Reply

  3. Julian Chen

    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!

    Reply

  5. Anura Ratnasiri

    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

    Reply

  6. Jomili

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *