Center across selection doesn't work on "smaller range" but only on "bigger range"

Question

I hope you're doing well. I am reaching out to the Stack Overflow community as I am currently experiencing beginner problem in programming in VBA in Excel. I am indeed trying to shorten and harmonize excels processes in my company.

CONTEXT
Among other commands, I am trying to program a macro to Center across selection (that can be pretty useful and which is a pain to right click go to Format etc.). I found the code online and somehow it works to some extent.

PROBLEM
Step 1
I select some cells. I run the macro (see code below). It centers across selection.
Step 2
I select a greater range of cells. It still center across selection.
Step 3
I select a smaller range of cells. It doesn't center across selection. It keeps the widest range used on this particular set of cells.

WHAT I HAVE TRIED SO FAR
I tried using the Selection.ClearFormats command ; didn't work (maybe not used well).

CODE

Sub center_across_selection()
'
' center_across_selection Macro
'
' Touche de raccourci du clavier: Ctrl+Shift+M
'
    With Selection
      If .HorizontalAlignment = xlCenterAcrossSelection Then
            .HorizontalAlignment = xlGeneral
        'converts regular text to centered across selection
        Else
            Selection.HorizontalAlignment = xlCenterAcrossSelection
        End If
    End With
End Sub

Thank you for your help!

first_step

second_step

Seems like each individual cell will get the "center across selection" alignment, and if you set that value on cells immediately next to cells which already have that setting (but might have no content), Excel will automatically extend the "selection" to include those other cells. — Tim Williams
Thank you for your reply. What would be a solution then, as clearing formats of the cells doesn't work for me? Can you come up with an idea? — InnovAnon

Tim Williams Tim Williams · Accepted Answer · 2018-10-23T17:13:30

Seems like each individual cell will get the "center across selection" alignment, and if you set that value on cells immediately next to cells which already have that setting (but have no content), Excel will automatically extend the "selection" to include those other cells.

Here's some code which will show the formats just below the selected cells:

Sub center_across_selection()
    Dim c As Range
    With Selection
        'show alignment "before"
        For Each c In .Cells
            c.Offset(1, 0) = c.HorizontalAlignment
        Next c
        Debug.Print .HorizontalAlignment = xlCenterAcrossSelection
        If .HorizontalAlignment = xlCenterAcrossSelection Then
            .HorizontalAlignment = xlGeneral
        Else
            .HorizontalAlignment = xlCenterAcrossSelection
        End If
        'show alignment "after"
        For Each c In .Cells
            c.Offset(2, 0) = c.HorizontalAlignment
        Next c
    End With
End Sub

As for a fix, you could check the cell immediately to the right of the last cell in your selection - if it's set as "center across selection" then set it to something else.

Sub center_across_selection2()
    Dim c As Range, rNext As Range
    With Selection
        If .HorizontalAlignment = xlCenterAcrossSelection Then
            .HorizontalAlignment = xlGeneral
        Else
            'check whether the next cell to the right has CAS and is empty
            Set rNext = .Cells(.Cells.Count).Offset(0, 1)
            If rNext.HorizontalAlignment = xlCenterAcrossSelection _
                                         And Len(rNext.Value) = 0 Then
                rNext.HorizontalAlignment = xlGeneral
            End If
            .HorizontalAlignment = xlCenterAcrossSelection
        End If
    End With
End Sub

Center across selection doesn't work on "smaller range" but only on "bigger range"

2 Answers