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.
Sub merge_cells() 'merge the range Range("A1:D1").Merge End Sub
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.
Sub unmerge_cells() 'you can also use B1,C1 or D1 to unmerge cells Range("A1").UnMerge End Sub
When you run this Sub Procedure in which the range A1:D1 is merged, it would be unmerged as a result.
Also Read: Multiple Ways to Merge Columns in Excel
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.
Sub merge_rows() 'merge the rows Range("2:5").Merge End Sub
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.
Sub merge_cols() Range("C:E").Merge End Sub
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.
Also Read: Clear Cell Content and Formatting using VBA
Sub mergeHorizontalCenterAlign() 'merge and align center horizontally Range("A1:E1").Merge Range("A1:E1").HorizontalAlignment = xlCenter End Sub
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.
Sub mergeVerticalCenterAlign() 'merge and align center vertically Range("A1:A4").Merge Range("A1:E1").VerticalAlignment = xlCenter End Sub
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.
Sub merge_across() 'merge across a range of cells Range("A1:D4").Merge Across:=True End Sub
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.
Sub merge_sheet2() 'merge cells of a worksheet Sheet2 Worksheets("Sheet2").Range("A1:A5").Merge End Sub
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.
Sub merge_combineCellContents() Dim combined As String Dim rng As Range Set rng = Range("A1:A3") For Each Cell In rng combined = combined & " " & Cell Next Cell With rng .Merge .Value = Trim(combined) .WrapText = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End Sub
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.