In this tutorial, we would learn about different ways to merge cells using VBA. We would also write a Sub Procedure that merges the cells with no loss of data in the cells that are not the upper left cell of the merged range.
So let us begin learning.
Merge Cells using VBA
We can use the Range object to specify the range of cells that we wish to merge and then use the Merge method of the Range object to merge the range. For instance, when you run this Sub Procedure, it merges the range A1:D1 of the active sheet.
'merge the range
This is the result.
Unmerge Cells using VBA
Use any of the cells of the merged range of cells to unmerge the entire range of cells. We have merged the range A1:D1. We can unmerge the range using any cell A1, B1, C1, or D1.
'you can also use B1,C1 or D1 to unmerge cells
When you run this Sub Procedure in which the range A1:D1 is merged, it would be unmerged as a result.
Merge Rows using VBA
We can merge the complete adjacent rows with the help of the Range object followed by the merge method. We need to specify the range as <First_Row>:<Last_Row>
For instance, if we wish to merge the four rows, from row 2 to row 5, we can use this procedure.
'merge the rows
Merge Columns using VBA
We can merge the entire adjacent columns by specifying the starting column at the left and the last column to the right of the merged column range.
Let us say we need to merge columns C, D, and E. So we would use this procedure.
Merge and Center the Contents Horizontally
Let us suppose we have the following range of cells A1:E1 and cell A1 contains a text string “ExcelUnocked”.
We want this to center align when we merge the range. Use this procedure to first merge and then center align the range.
'merge and align center horizontally
Range("A1:E1").HorizontalAlignment = xlCenter
Merge and Center the Contents Vertically
We can first merge the cells and then center the contents vertically. Let us say we have a range A1:A4 and A1 contains the text string “ExcelUnlocked”.
This Sub Procedure would merge the range A1:A4 and the content in cell A1 would be vertically aligned in the center of the merged range.
'merge and align center vertically
Range("A1:E1").VerticalAlignment = xlCenter
Merge Cells Across a Range
We can merge the cells across a range. All we need to do is, set the across parameter of the merge method to True.
Let us say we want to merge across the range A1:D4. Use the following procedure for this.
'merge across a range of cells
Merge Cells of Specific Worksheet
By now we have been merging the cells of the active worksheet. We can also merge the cells of any particular worksheet with the help of the Worksheets Collection.
Let us say you wish to merge the range in the worksheet named Sheet2. Use this code.
'merge cells of a worksheet Sheet2
As a result, this merges the range A1:A5 in Sheet2 without actually opening it.
Merging and Combining the Cell Contents
When we merge the cells that contain cell values, all the cell values are lost only except the upper left cell of the unmerged range which becomes the cell value for the merged range.
The contents of cell A2 and A3 is lost. We can define a macro that would combine the cell contents and then merge the cells. The merged cell would display the combined content of all the merged cells.
Use this Sub Procedure.
Dim combined As String
Dim rng As Range
Set rng = Range("A1:A3")
For Each Cell In rng
combined = combined & " " & Cell
.Value = Trim(combined)
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
As a result, when you run this Sub Procedure, it combines the content of range A1:A3, merges them, and then assigns the combined content to the merged cell. It would wrap the text and center it horizontally and vertically.
This brings us to end.
Thank you for reading.