How to Merge cells using Excel VBA

Merging cells in Excel is useful when you want to combine multiple cells into one larger cell. It can be used to title, organize data, or format the look of your worksheet.

Excel VBA is a programming language used to automate tasks in Microsoft Excel. One task that is often done in Excel is to combine cells, to unite several cells into one large cell. Merging cells can be useful for creating specific headings, tables, or data formats.

There are several ways to combine cells using Excel VBA, namely:

  • Use the “Merge” property of the “Range” object.
  • Use the “MergeCells” method of the “Range” object.
  • Use the “MergeArea” method of the “Range” object.
  • Use the “UnMerge” method of the “Range” object to unmerge cells.
vba
vba

This article will cover these methods in detail, along with code examples and results. We will also discuss some things to note when merging cells using Excel VBA.

Using the “Merge” Property of a “Range” Object

The “Merge” property of the “Range” object is used to set or return a Boolean value, indicating whether a range of cells is combined or not. A value of “True” means a range of cells is merged, while a False value means a range of cells is not merged.

To merge cells using the “Merge” property, we can assign “True” values to those properties for the range of cells we want to combine. The following code sample shows how to merge cells A1 through C3 using the Merge property.

Sub MergeCellsUsingMergeProperty()
 'Combining cells A1 to C3
 Range( “A1:C3” ). Merge = True
End Sub

We can see that cells A1 through C3 have been merged into one large cell, and the value from cell A1 remains displayed in the middle of the merged cell.

To unmerge cells usthe ing “Merge” property, we can assign a “False” value to that property for the range of cells we want to split. The following code sample shows how to unmerge cells A1 through C3 using the “Merge” property.

Sub UnMergeCellsUsingMergeProperty()
 'Unmerge cells A1 through C3
 Range( “A1:C3” ). Merge = False
End Sub

We can see that cells A1 through C3 have been separated into individual cells, and the value from cell A1 remains displayed in cell A1.

Using the “MergeCells” Method of the “Range” Object

The “MergeCells” method of “Range” objects is used to merge or unmerge selected cell ranges. This method has no arguments and only works on the range of cells selected by the user.

To merge cells using the “MergeCells” method, we can select the range of cells we want to combine, and then run the method. The following code sample shows how to combine cells D1 through F3 using the “MergeCells” method.

Sub MergeCellsUsingMergeCellsMethod()
 'Select cells D1 through F3
 Range( “D1:F3” ). Select
 'Merge selected cells
 Selection.MergeCells
End Sub

We can see that cells D1 through F3 have been merged into one large cell, and the value from cell D1 remains displayed in the middle of the merged cell.

To unmerge cells using the “MergeCells” method, we can select the range of cells that we want to split, and then run the method. The following code sample shows how to unmerge cells D1 through F3 using the “MergeCells” method.

Sub UnMergeCellsUsingMergeCellsMethod()
 'Select cells D1 through F3
 Range( “D1:F3” ). Select
 'Unmerge selected cells
 Selection.MergeCells
End Sub

We can see that cells D1 through F3 have been separated into individual cells, and the value from cell D1 remains displayed in cell D1.

Using the “MergeArea” Method of the “Range” Object

The “MergeArea” method of the “Range” object is used to return a “Range” object that represents a range of merged cells containing a specific cell. This method has no arguments and only works on cells that are already combined with other cells.

To merge cells using the “MergeArea” method, we can use the “Merge” property or “MergeCells” method first to combine the desired range of cells, and then use the “MergeArea” method to refer to the merged cell range. The following code sample shows how to combine cells G1 through I3 using the “Merge” property, and then use the “MergeArea” method to refer to that range of merged cells.

Sub MergeCellsUsingMergeAreaMethod()
 'Combining cells G1 to I3
 Range( “G1:I3” ). Merge = True
 'Refers to a range of merged cells containing G2 cells
 Range( “G2” ). MergeArea.Select
End Sub

We can see that cells G1 through I3 have been merged into one large cell, and the value from cell G1 remains displayed in the middle of the merged cell. We can also see that the range of merged cells has been selected by our code, by using the “MergeArea” method to refer to the range of merged cells containing cell G2.

To unmerge cells using the “MergeArea” method, we can use the “Merge” property or “MergeCells” method first to unmerge the desired range of cells, and then use the “MergeArea” method to refer to that range of individual cells. The following code sample shows how to unmerge cells G1 through I3 using the “Merge” property, and then use the “MergeArea” method to refer to that range of individual cells.

Sub UnMergeCellsUsingMergeAreaMethod()
 'Unmerge cells G1 to I3
 Range( “G1:I3” ). Merge = False
 'Refers to the range of individual cells containing G2 cells
 Range( “G2” ). MergeArea.Select
End Sub

We can see that cells G1 through I3 have been separated into individual cells, and the value from cell G1 remains displayed in cell G1. We can also see that the range of those individual cells has been selected by our code, using the MergeArea method to refer to the range of individual cells containing cell G2.

Other Interesting Articles

Using the UnMerge Method of an Object “Range”

The “UnMerge” method of the “Range” object is used to unmerge all merged cells in a range of cells. This method has no arguments and only works on ranges of cells that contain at least one merged cell.

To unmerge cells using the “UnMerge” method, we can run the method for the range of cells we want to split. The following code sample shows how to unmerge all merged cells in an active worksheet using the method “UnMerge”.

Sub UnMergeCellsUsingUnMergeMethod()
 'Unmerge all merged cells in the active worksheet
 ActiveSheet.Cells.UnMerge
End Sub

We can see that all merged cells in the active worksheet have been separated into individual cells, and the values of those cells remain displayed in their original locations.

Things to Note When Merging Cells Using Excel VBA

When we combine cells using Excel VBA, there are some things we need to pay attention to, namely:

  • Only values from the upper-left cell will be displayed in the merged cell. Values from other merged cells will be lost. Therefore, we’d better make sure that the value we want to display is in the upper-left cell before merging cells.
  • Merging cells can affect the format and layout of our data. For example, if we combine multiple columns, the column width will be adjusted to the width of the widest column. If we combine multiple rows, the row height will be adjusted to the height of the highest row. If we combine multiple cells containing formulas, those formulas will be deleted and only the resulting values will be displayed. Therefore, we should examine the impact of merging cells on our data before doing so.
  • Merging cells can cause reference errors if we use a range of merged cells as arguments or criteria in Excel functions. For example, if we use the “SUMIF” function to sum the values in a range based on a criterion, and the range of criteria contains merged cells, then the result will be incorrect or invalid. Therefore, we’d better avoid using Excel functions that rely on range references when merging cells.

Thus the article on how to combine cells using Excel VBA. Hope this article is helpful and can help you in automating your tasks in Excel. Thank you for reading this article to the end.

Latest Articles

Related Articles