How to Merge cells using Excel VBA

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