Merge and Unmerge Cells using Excel VBA

Although Excel is a great tool for automating your work, Visual Basic for Applications (VBA) is even better tool to achieve this. All the things that are done in Excel can basically be done through VBA.

In the example below, we will show how to use VBA for merging and unmerging cells.

Merge and Unmerge Cells using VBA

For our example, we will use the sales figures presented in the first three columns:

Obviously, the title should go through the range A1:C1, and to do that, we need to merge these cells. To do this, we will click cell A1, then select the cells B1 and C1 as well. After that, we will go to the Home tab, Alignment section, and choose one of the first three options: Merge & Center, Merge Across, or Merge Cells:

We will choose Merge & Center option, and then we will use Unmerge Cells to return to the original view.

We can achieve the same thing with VBA. To do this, we will first open the VBA by pressing ALT + F11 on our keyboard. On the window that appears, we will choose Insert tab and then Module:

We will have a new window created on the right side, and we will insert the following code for merging the cells:

Sub MergeCells()  
    Dim rng As Range
    Set rng = Range("A1:C1")
    rng.MergeCells = True
    rng.HorizontalAlignment = xlCenter
End Sub

Sub MergeCells()

Dim rng As Range

Set rng = Range("A1:C1")

rng.MergeCells = True

rng.HorizontalAlignment = xlCenter

End Sub

This is a simple code on which we first declare a rng as a range variable, and then set it to be equal to the range that we want to merge. Then we use MergeCells command and set it to true for our range. We also use HorizontalAlignment to center our range. When we run the code by pressing F5, this is what we will end up with:

If we did not want to hardcode the range, we can also give an option for a user to select the range that he wants to merge and center. For this, our code will be:

Sub MergeAndCenterSelectedCells()
    Dim sRange As Range
    On Error Resume Next
    Set sRange = Application.InputBox("Select a range of cells to merge and center:", Type:=8)
   On Error GoTo 0
    If Not sRange Is Nothing Then
        sRange.MergeCells = True
        sRange.HorizontalAlignment = xlCenter
    End If
End Sub

Sub MergeAndCenterSelectedCells()

Dim sRange As Range

On Error Resume Next

Set sRange = Application.InputBox("Select a range of cells to merge and center:", Type:=8)

On Error GoTo 0

If Not sRange Is Nothing Then

sRange.MergeCells = True

sRange.HorizontalAlignment = xlCenter

End If

End Sub

In this case, we have sRange variable that will store any range that a user selects through the input box. We also insert the error handing in case user decides not to choose any range.

For the last part, we insert the If formula that will merge and center any potentially user chosen range.

We will unmerge cells A1:C1, then execute our code by pressing F5 on our keyboard. Once we do this, the following window will appear:

We will select range A1:C1, and then click OK:

Our cells will be merged:

If a user simply opted out of the input box, nothing would have happened, because the error handling would take him out of the dialog box.

If we want to Unmerge our cells through VBA, we use a similar code as we did in the example above:

Sub UnmergeCells()
    Dim rng As Range
    Set rng = Range("A1:C1")
    rng.UnMerge
End Sub

Sub UnmergeCells()

Dim rng As Range

Set rng = Range("A1:C1")

rng.UnMerge

End Sub

We declare the variable as a range, set it to be equal to the range of cells that we want to unmerge, and simply use UnMerge command to achieve it. We will execute the code by pressing F5, and our range will be unmerged:

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 15