How to Merge Cells in Excel using a VBA Code

Home ➜ VBA ➜ How to Merge Cells in Excel using a VBA Code

In VBA, there is a “MERGE” method that you can use to merge a range of cells or even multiple ranges into one. This method has an argument “Across” which is optional. If you specify TRUE it will merge each row in the range separately, and if you specify FALSE it will merge the entire range as one.

Steps to follow to use VBA to Merge Cells:

1. First, you need to define the range of cells that you want to merge.
   
2. After that, type a (.) dot to get the list of properties and methods and select “Merge” from that list or you can type it directly.
   
3. After that, you need to define the argument “Across” as TRUE or FALSE.
   
4. In the end, run that code to merge the range.

Here’s the code.

Range("A1:A2").Merge False

And if you want to merge rows across the range you can use TRUE for the argument across.

Selection.Merge Across:=True

In the above code, you have the selection as the range, the merge method, and across as true. And it has merged all the rows in the selected range.

Now, let’s say you want to merge the range of cells from another worksheet, you don’t need to activate it first. You can simply refer to the worksheet first and then use the merge methods with the renege that you want to merge.

Worksheets("Sheet1").Range("A1:A2").Merge

In the same way, you can refer to a workbook as well by specifying the name of the workbook.

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1:A2").Merge

Merge a Range and Align the Cell Content

Now when you merge two or more cells you need to align the content that you have in the cell. Look at the below snapshot where you have cell A1 and A2 merged and the value in the cells is aligned bottom. To deal with this problem, you can use the alignment property and align the cell content to the center and middle so that it’ll be easy for you to read it.

With Range("A1:A2")

    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter

End With

Combine the Text and then Merge

Let talk about a real-life scenario. You have text in a range of cells and you to merge those cells but don’t want to lose the content that you have. Well, you can write a code loop through the entire range using for each and combine text and then add it to the merged range in the end.

1. In the first part, you have variables to use to store values from the range and define itself.
2. In the second part, you have a LOOP to combine value from each cell to create a new string with that.
3. In the third part, merge the range, add the combined string, apply the wrap, and alignment of the cell content.

Here’s the full code.

Sub vba_merge_with_values()

Dim val As String
Dim rng As Range

Set rng = Range("A1:A10")

For Each Cell In rng
    val = val & " " & Cell.Value
Next Cell

With rng
    .Merge
    .Value = Trim(val)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With

End Sub