CODEWITHSUNDEEP
vbaexcel
detroitwilly
detroitwilly

Reputation: 821

Merging and Centering Cells in VBA

I recently set out to write a simple macro the merge/unmerge cells with a keyboard shortcut.

The macro is working currently with the following code:

If Selection.MergeCells = True Then
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
ElseIf Selection.MergeCells = False Then
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Selection.Merge
End If

This works perfectly well, but I originally had a much simpler sub that didn't work. It was:

If Selection.MergeCells = False Then Selection.Merge
If Selection.MergeCells = True Then Selection.UnMerge

This two-line version only worked to merge cells, not to unmerge them. Does anyone know why this was happening?

Thanks.

-Sean

Upvotes: 0

Views: 3832

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You need an ELSE

Sub qwerty()
    If Selection.MergeCells = False Then
        Selection.Merge
    Else
        Selection.UnMerge
    End If
End Sub

Upvotes: 2

Related Questions