Record a Macro to Merge/DeMerge cells in Excel

In our earlier posts we discussed how to record a Macro and we discussed one example to record a macro to AutoFit. Lets take one more example to create a Macro to Merge and De-Merge the selected cells quickly, which is otherwise a long procedure.

Steps to create a Macro for Merge

1. Start Macro Recorder and name it as "MergeCells"
2. Select the cells you want to merge. You can also select two blank cells.
3. Click on "Merge and Centre" button on the Formatting toolbar.
4. Stop the Macro Recorder.

Press ALT + F11 and open the Visual Basic Editor. You will see that the following code is generated.

Sub MergeCells()
'
' MergeCells Macro
' Macro recorded 3/7/2008 by Ravi Sagar
'
 
'
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
End Sub

Do not worry much about all the lines written here. The only thing that should be your concern is the line Selection.Merge, it tells Excel to Merge the selected cells.

Steps to create a Macro for DeMerge

1. Start Macro Recorder and name it as "DeMergeCells"
2. Select the cell you want to DeMerge. You can also select Merged Cell from the above operation.
3. Right Click on the selected cells > Format Cells > Alignment Tab > Under Text Control, uncheck the "Merge Cells" check box.
4. Stop the Macro Recorder.

Press ALT + F11 and open the Visual Basic Editor. You will see that the following code is generated.

Sub DeMergeCells()
'
' DeMergeCells Macro
' Macro recorded 3/7/2008 by Ravi Sagar
'
 
'
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
End Sub

In this code the statement .MergeCells = False unmerges the selected cell.

I hope now you will get good idea about recording macros in the Excel sheets. I our future post we will discuss the recording of macros in OpenOffice.