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:

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:

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:

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:

Posted in vba